1 2 Previous Next 20 Replies Latest reply: Feb 23, 2012 9:56 AM by 919185 Go to original post RSS
      • 15. Re: XML query tuning
        Marco Gralike
        In the initial example given by the requester, I was hoping to see less I/O due to less index scan access. Still not ideal, but then again no alternatives in 11.1 like a structured XMLindex.

        Probably, also guessing like you regarding the table/data setup, an XMLType Object Relational storage could help and/or combined with XML reference partitioning (/equi-partitioning - so if large volume support is needed of not so volatile data). In the case of a XMLType Object Relational setup, no XMLIndex would be needed.

        Edited by: Marco Gralike on Feb 1, 2012 11:50 PM
        • 16. Re: XML query tuning
          Marco Gralike
          Can't find Mark's example for use in 11.1.0.7.0 (a version which first supported this although not documented in manuals) here on the OTN forum (sure there was one). I think it was a bit like the following:
          /*
             2008 - Equipartitioning of XML nested table data.
          */
          
          -- Creating a test user
          
          SQL> connect sys as sysdba
          Connected.
          
          SQL> purge dba_recyclebin;
          
          DBA Recyclebin purged.
          
          SQL> drop user test cascade;
          drop user test cascade
                    *
          ERROR at line 1:
          ORA-01918: user 'TEST' does not exist
          
          SQL> create user test identified by test;
          
          User created.
          
          SQL> grant xdbadmin, dba to test;
          
          Grant succeeded.
          
          SQL> connect test/test
          Connected.
          
          SQL> set long 100000000
          
          SQL> purge recyclebin;
          
          Recyclebin purged.
          
          SQL> alter session set recyclebin=OFF;
          
          Session altered.
          
          -- Registering the XML Schema with default varchar dbms_xmlschema.registerschema values
          
          SQL> begin
            2  dbms_xmlschema.registerschema(
            3  'EmpProjectInfo.xsd',
            4  '<?xml version="1.0" encoding="UTF-8"?>
            5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="false">
            6    <xs:element name="EmployeeProjectInfo" type="EmpType"/>
            7    <!-- does not have maintainDom=false -->
            8    <xs:complexType name="EmpType">
            9       <xs:sequence>
           10          <xs:element name="Reference" type="xs:positiveInteger" xdb:SQLName="EMPLOYEE_REFERENCE_ID"/>
           11          <xs:element name="Employees" type="EmployeesType" xdb:SQLName="EMPLOYEES"/>
           12        </xs:sequence>
           13      </xs:complexType>
           14    <xs:complexType name="EmployeesType" xdb:SQLType="XDB_EMPLOYEES_TYPE" xdb:maintainDOM="false">
           15       <xs:sequence>
           16          <xs:element name="Employee" maxOccurs="unbounded" xdb:SQLName="EMPLOYEE" xdb:maintainOrder="false" xdb:SQLCollType="XDB_EMPLOYEE_COLLECTION">
           17              <xs:complexType>
           18                 <xs:complexContent>
           19                    <xs:extension base="EmployeeType">
           20                       <xs:attribute name="id"/>
           21                    </xs:extension>
           22                 </xs:complexContent>
           23              </xs:complexType>
           24           </xs:element>
           25       </xs:sequence>
           26    </xs:complexType>
           27    <xs:complexType name="EmployeeType" xdb:SQLType="XDB_EMPLOYEE_TYPE" xdb:maintainDOM="false">
           28       <xs:sequence>
           29          <xs:element name="ProjectDetails" maxOccurs="unbounded" xdb:SQLName="PROJECT_DETAILS" xdb:maintainOrder="false" xdb:SQLCollType="XDB_DETAILS_COLLECTION">
           30              <xs:complexType>
           31                 <xs:complexContent>
           32                    <xs:extension base="ProjectDetails">
           33                       <xs:attribute name="id"/>
           34                    </xs:extension>
           35                 </xs:complexContent>
           36              </xs:complexType>
           37           </xs:element>
           38       </xs:sequence>
           39    </xs:complexType>
           40    <xs:complexType name="ProjectDetails" xdb:SQLType="XDB_PROJECT_DETAILS_TYPE" xdb:maintainDOM="false">
           41       <xs:sequence>
           42          <xs:element name="ProjectNumber" type="xs:positiveInteger" xdb:SQLName="PROJECT_NUMBER"/>
           43          <xs:element name="ProjectHours" type="xs:integer" xdb:SQLName="PROJECT_HOURS"/>
           44          <xs:element name="ProjectFunction" type="xs:string" xdb:SQLName="PROJECT_FUNCTION"/>
           45      </xs:sequence>
           46    </xs:complexType>
           47  </xs:schema>');
           48  END;
           49  /
          
          PL/SQL procedure successfully completed.
          
          -- User objects created
          
          SQL> select * from user_objects;
          
          OBJECT_NAME                                        SUBOBJECT_NAME                 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
          -------------------------------------------------- ------------------------------ --------- -------------- -------------------
          CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS  T G S NAMESPACE EDITION_NAME
          ------------------ ------------------ ------------------- ------- - - - --------- ------------------------------
          XDB_PROJECT_DETAILS_TYPE                                                              70220                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          ProjectDetails652_T                                                                   70221                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          XDB_DETAILS_COLLECTION                                                                70222                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          XDB_EMPLOYEE_TYPE                                                                     70223                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          Employee653_T                                                                         70224                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          XDB_EMPLOYEE_COLLECTION                                                               70225                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          XDB_EMPLOYEES_TYPE                                                                    70226                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          EmpType654_T                                                                          70227                TYPE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1
          
          EmployeeProjectInfo655_TAB                                                            70228          70228 TABLE
          02-JAN-08          02-JAN-08          2008-01-02:16:17:00 VALID   N Y N         1
          
          SYSNTH9VFj9ykTZ2/a5L9qHhd/g==                                                         70229          70229 TABLE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         1
          
          SYSNTHvj89wtDROGoDFsAArfZlQ==                                                         70230          70230 TABLE
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         1
          
          SYS_C009924                                                                           70231          70231 INDEX
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         4
          
          SYS_C009925                                                                           70238          70238 INDEX
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         4
          
          SYS_LOB0000070228C00008$$                                                             70236          70236 LOB
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         8
          
          SYS_LOB0000070228C00005$$                                                             70234          70234 LOB
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         8
          
          SYS_LOB0000070228C00004$$                                                             70232          70232 LOB
          02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         8
          
          SYS_C009926                                                                           70239          70239 INDEX
          02-JAN-08          02-JAN-08          2008-01-02:16:17:00 VALID   N Y N         4
          
          EmployeeProjectInfo655_TAB$xd                                                         70240                TRIGGER
          02-JAN-08          02-JAN-08          2008-01-02:16:17:00 VALID   N N N         3
          
          
          18 rows selected.
          • 17. Re: XML query tuning
            Marco Gralike
            -- Creating the Equipartitioning object structures
            
            SQL> CREATE TABLE PART_DEMO OF SYS.XMLTYPE
              2    XMLSCHEMA "EmpProjectInfo.xsd" ELEMENT "EmployeeProjectInfo"
              3  nested table xmldata."EMPLOYEES"."EMPLOYEE" STORE AS EMP_PROJ_TAB
              4      (nested table PROJECT_DETAILS STORE AS PROJ_DETAILS_TAB)
              5  partition by range( xmldata."EMPLOYEE_REFERENCE_ID")
              6  (partition P11 values less than (50)
              7  NESTED TABLE xmldata."EMPLOYEES"."EMPLOYEE" STORE AS EMP_PROJ_P11 (STORAGE (MINEXTENTS 13)
              8     nested table PROJECT_DETAILS STORE AS PROJ_DETAILS_P11),
              9  partition P21 values less than (MAXVALUE)
             10  NESTED TABLE xmldata."EMPLOYEES"."EMPLOYEE" STORE AS EMP_PROJ_P21 (STORAGE (MINEXTENTS 13)
             11      nested table PROJECT_DETAILS STORE AS PROJ_DETAILS_P21)
             12  );
            
            Table created.
            
            SQL> select dbms_metadata.get_ddl('TABLE','PART_DEMO') from dual;
            
            DBMS_METADATA.GET_DDL('TABLE','PART_DEMO')
            --------------------------------------------------------------------------------
            
            CREATE TABLE "TEST"."PART_DEMO" OF "SYS"."XMLTYPE"
            XMLSCHEMA "EmpProjectInfo.xsd" ELEMENT "EmployeeProjectInfo"
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
              STORAGE(BUFFER_POOL DEFAULT)
              TABLESPACE "USERS"
            NESTED TABLE "XMLDATA"."EMPLOYEES"."EMPLOYEE" STORE AS "EMP_PROJ_TAB"
             (PCTFREE 0 PCTUSED 0 INITRANS 0 MAXTRANS 0 LOGGING
              TABLESPACE "SYSTEM"
             NESTED TABLE "PROJECT_DETAILS" STORE AS "PROJ_DETAILS_TAB"
             (PCTFREE 0 PCTUSED 0 INITRANS 0 MAXTRANS 0 LOGGING
              TABLESPACE "SYSTEM" ) RETURN AS VALUE) RETURN AS VALUE
              PARTITION BY RANGE ("XMLDATA"."EMPLOYEE_REFERENCE_ID")
             (PARTITION "P11"  VALUES LESS THAN (50)
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS ,
             PARTITION "P21"  VALUES LESS THAN (MAXVALUE)
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
              )
             TABLESPACE "USERS" NOCOMPRESS
             )
            
            1 row selected.
            
            -- Creating simple test data
            
            SQL> insert into "PART_DEMO"
              2  values
              3  (xmltype(
              4  '<EmployeeProjectInfo>
              5     <Reference>1</Reference>
              6     <Employees>
              7       <Employee id="111">
              8         <ProjectDetails>
              9           <ProjectNumber>200801010001</ProjectNumber>
             10           <ProjectHours>38</ProjectHours>
             11           <ProjectFunction>Project DBA</ProjectFunction>
             12        </ProjectDetails>
             13       </Employee>
             14     </Employees>
             15  </EmployeeProjectInfo>'));
            
            1 row created.
            
            SQL> insert into "PART_DEMO"
              2  values
              3  (xmltype(
              4  '<EmployeeProjectInfo>
              5     <Reference>51</Reference>
              6     <Employees>
              7       <Employee id="001">
              8         <ProjectDetails>
              9           <ProjectNumber>200801010001</ProjectNumber>
             10           <ProjectHours>24</ProjectHours>
             11           <ProjectFunction>Project Manager</ProjectFunction>
             12        </ProjectDetails>
             13       </Employee>
             14     </Employees>
             15  </EmployeeProjectInfo>'));
            
            1 row created.
            
            SQL> commit;
            
            Commit complete.
            
            -- Creating schema wide stats
            
            SQL> exec dbms_stats.gather_schema_stats('TEST')
            
            PL/SQL procedure successfully completed.
            
            SQL> set autotrace on
            
            -- Select examples and outcome
            
            SQL> select u.EMPLOYEENUMBER
              2  from   "PART_DEMO" t
              3  ,      XMLTABLE('/EmployeeProjectInfo/Employees'
              4                  PASSING value(t)
              5                  COLUMNS employeenumber VARCHAR2(15) PATH 'Employee/@id'
              6                  ) u
              7  ;
            
            EMPLOYEENUMBER
            ---------------
            111
            001
            
            2 rows selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 493488300
            
            ------------------------------------------------------------------------------------------------------------
            | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |              |     2 |    38 |     4   (0)| 00:00:01 |       |       |
            |   1 |  SORT AGGREGATE             |              |     1 |    38 |            |          |       |       |
            |   2 |   PARTITION REFERENCE SINGLE|              |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  3 |    TABLE ACCESS FULL        | EMP_PROJ_TAB |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |   4 |  PARTITION RANGE ALL        |              |     2 |    38 |     4   (0)| 00:00:01 |     1 |     2 |
            |*  5 |   TABLE ACCESS FULL         | PART_DEMO    |     2 |    38 |     4   (0)| 00:00:01 |     1 |     2 |
            ------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               3 - filter("NESTED_TABLE_ID"=:B1)
               5 - filter("T"."SYS_NC00011$" IS NOT NULL)
            
            
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      0  db block gets
                    169  consistent gets
                      0  physical reads
                      0  redo size
                    458  bytes sent via SQL*Net to client
                    385  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      2  rows processed
            
            
            SQL> select u.projectdetails
              2  from   "PART_DEMO" t
              3  ,      XMLTABLE('//Employee'
              4                  PASSING value(t)
              5                  COLUMNS projectdetails XMLTYPE PATH '*'
              6                  ) u
              7  ;
            
            PROJECTDETAILS
            ----------------------------------------------------------------------
            <ProjectDetails>
              <ProjectNumber>200801010001</ProjectNumber>
              <ProjectHours>38</ProjectHours>
              <ProjectFunction>Project DBA</ProjectFunction>
            </ProjectDetails>
            
            <ProjectDetails>
              <ProjectNumber>200801010001</ProjectNumber>
              <ProjectHours>24</ProjectHours>
              <ProjectFunction>Project Manager</ProjectFunction>
            </ProjectDetails>
            
            
            2 rows selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 1750243350
            
            ------------------------------------------------------------------------------------------------------------------------
            | Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ------------------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT                    |                  |     2 |   150 |    36   (0)| 00:00:01 |       |       |
            |   1 |  SORT AGGREGATE                     |                  |     1 |    42 |            |          |       |       |
            |   2 |   PARTITION REFERENCE SINGLE        |                  |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  3 |    TABLE ACCESS FULL                | PROJ_DETAILS_TAB |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |   4 |  NESTED LOOPS                       |                  |       |       |            |          |       |       |
            |   5 |   NESTED LOOPS                      |                  |     2 |   150 |    36   (0)| 00:00:01 |       |       |
            |   6 |    PARTITION REFERENCE ALL          |                  |     2 |    68 |    34   (0)| 00:00:01 |     1 |     2 |
            |   7 |     TABLE ACCESS FULL               | EMP_PROJ_TAB     |     2 |    68 |    34   (0)| 00:00:01 |     1 |     2 |
            |*  8 |    INDEX UNIQUE SCAN                | SYS_C009929      |     1 |       |     0   (0)| 00:00:01 |       |       |
            |   9 |   TABLE ACCESS BY GLOBAL INDEX ROWID| PART_DEMO        |     1 |    41 |     1   (0)| 00:00:01 | ROWID | ROWID |
            ------------------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               3 - filter("NESTED_TABLE_ID"=:B1)
               8 - access("NESTED_TABLE_ID"="T"."SYS_NC0001200013$")
            
            
            Statistics
            ----------------------------------------------------------
                     64  recursive calls
                      0  db block gets
                    248  consistent gets
                      0  physical reads
                      0  redo size
                   2859  bytes sent via SQL*Net to client
                   1620  bytes received via SQL*Net from client
                     11  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      2  rows processed
            
            SQL> select u.EMPLOYEENUMBER
              2  from   "PART_DEMO" t
              3  ,      XMLTABLE('/EmployeeProjectInfo/Employees'
              4                  PASSING value(t)
              5                  COLUMNS employeenumber VARCHAR2(15) PATH 'Employee/@id'
              6                  ) u
              7  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 51
              8  ;
            
            EMPLOYEENUMBER
            ---------------
            001
            
            1 row selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 3531522478
            
            ------------------------------------------------------------------------------------------------------------
            | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |              |     1 |    22 |     3   (0)| 00:00:01 |       |       |
            |   1 |  SORT AGGREGATE             |              |     1 |    38 |            |          |       |       |
            |   2 |   PARTITION REFERENCE SINGLE|              |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  3 |    TABLE ACCESS FULL        | EMP_PROJ_TAB |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |   4 |  PARTITION RANGE SINGLE     |              |     1 |    22 |     3   (0)| 00:00:01 |     2 |     2 |
            |*  5 |   TABLE ACCESS FULL         | PART_DEMO    |     1 |    22 |     3   (0)| 00:00:01 |     2 |     2 |
            ------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               3 - filter("NESTED_TABLE_ID"=:B1)
               5 - filter("T"."SYS_NC00009$"=51 AND "T"."SYS_NC00011$" IS NOT NULL)
            
            
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      0  db block gets
                    131  consistent gets
                      0  physical reads
                      0  redo size
                    418  bytes sent via SQL*Net to client
                    385  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            
            SQL> select *
              2  from "PART_DEMO" t
              3  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 1
              4  ;
            
            SYS_NC_ROWINFO$
            ----------------------------------------------------------------------
            <EmployeeProjectInfo>
              <Reference>1</Reference>
              <Employees>
                <Employee id="111">
                  <ProjectDetails>
                    <ProjectNumber>200801010001</ProjectNumber>
                    <ProjectHours>38</ProjectHours>
                    <ProjectFunction>Project DBA</ProjectFunction>
                  </ProjectDetails>
                </Employee>
              </Employees>
            </EmployeeProjectInfo>
            
            
            1 row selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 3358334024
            
            ---------------------------------------------------------------------------------------------------------------
            | Id  | Operation                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ---------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT           |                  |     1 |    41 |     3   (0)| 00:00:01 |       |       |
            |   1 |  PARTITION REFERENCE SINGLE|                  |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  2 |   TABLE ACCESS FULL        | PROJ_DETAILS_TAB |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |   3 |  PARTITION REFERENCE SINGLE|                  |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  4 |   TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |   5 |  PARTITION RANGE SINGLE    |                  |     1 |    41 |     3   (0)| 00:00:01 |     1 |     1 |
            |*  6 |   TABLE ACCESS FULL        | PART_DEMO        |     1 |    41 |     3   (0)| 00:00:01 |     1 |     1 |
            ---------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               2 - filter("NESTED_TABLE_ID"=:B1)
               4 - filter("NESTED_TABLE_ID"=:B1)
               6 - filter("T"."SYS_NC00009$"=1)
            
            
            Statistics
            ----------------------------------------------------------
                    184  recursive calls
                      0  db block gets
                    179  consistent gets
                      0  physical reads
                      0  redo size
                  27109  bytes sent via SQL*Net to client
                   5616  bytes received via SQL*Net from client
                     53  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            
            SQL> select extractvalue(value(t),'//ProjectFunction') as "PROJECT_FUNCTION"
              2  from "PART_DEMO" t
              3  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 1;
            
            PROJECT_FUNCTION
            ----------------
            Project DBA
            
            1 row selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 3474034851
            
            ----------------------------------------------------------------------------------------------------------------
            | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ----------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |                  |     1 |    20 |     3   (0)| 00:00:01 |       |       |
            |   1 |  PARTITION REFERENCE SINGLE |                  |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  2 |   TABLE ACCESS FULL         | PROJ_DETAILS_TAB |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  3 |  FILTER                     |                  |       |       |            |          |       |       |
            |   4 |   PARTITION REFERENCE SINGLE|                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  5 |    TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |   6 |  PARTITION RANGE SINGLE     |                  |     1 |    20 |     3   (0)| 00:00:01 |     1 |     1 |
            |*  7 |   TABLE ACCESS FULL         | PART_DEMO        |     1 |    20 |     3   (0)| 00:00:01 |     1 |     1 |
            ----------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               2 - filter("NESTED_TABLE_ID"=:B1)
               3 - filter( (SELECT "SYS_ALIAS_4"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_4"
                          WHERE "NESTED_TABLE_ID"=:B1) IS NOT NULL)
               5 - filter("NESTED_TABLE_ID"=:B1)
               7 - filter("T"."SYS_NC00009$"=1)
            
            
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      0  db block gets
                     52  consistent gets
                      0  physical reads
                      0  redo size
                    454  bytes sent via SQL*Net to client
                    385  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            
            SQL> select extractvalue(value(t),'//ProjectFunction') as "PROJECT_FUNCTION"
              2  from "PART_DEMO" t
              3  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 51;
            
            PROJECT_FUNCTION
            ----------------
            Project Manager
            
            1 row selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 3474034851
            
            ----------------------------------------------------------------------------------------------------------------
            | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            ----------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT            |                  |     1 |    20 |     3   (0)| 00:00:01 |       |       |
            |   1 |  PARTITION REFERENCE SINGLE |                  |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  2 |   TABLE ACCESS FULL         | PROJ_DETAILS_TAB |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  3 |  FILTER                     |                  |       |       |            |          |       |       |
            |   4 |   PARTITION REFERENCE SINGLE|                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  5 |    TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |   6 |  PARTITION RANGE SINGLE     |                  |     1 |    20 |     3   (0)| 00:00:01 |     2 |     2 |
            |*  7 |   TABLE ACCESS FULL         | PART_DEMO        |     1 |    20 |     3   (0)| 00:00:01 |     2 |     2 |
            ----------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               2 - filter("NESTED_TABLE_ID"=:B1)
               3 - filter( (SELECT "SYS_ALIAS_4"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_4"
                          WHERE "NESTED_TABLE_ID"=:B1) IS NOT NULL)
               5 - filter("NESTED_TABLE_ID"=:B1)
               7 - filter("T"."SYS_NC00009$"=51)
            
            
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      0  db block gets
                     52  consistent gets
                      0  physical reads
                      0  redo size
                    458  bytes sent via SQL*Net to client
                    385  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            
            SQL> select extractvalue(value(t),'//ProjectHours') as "PROJECT_HOURS"
              2  from "PART_DEMO" t
              3  where  extractvalue(value(t),'//ProjectFunction') = 'Project Manager'
              4  ;
            
            PROJECT_HOURS
            -------------
                       24
            
            1 row selected.
            
            
            Execution Plan
            ----------------------------------------------------------
            Plan hash value: 2850148909
            
            -----------------------------------------------------------------------------------------------------------------
            | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
            -----------------------------------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT             |                  |     2 |    34 |    25   (0)| 00:00:01 |       |       |
            |   1 |  PARTITION REFERENCE SINGLE  |                  |     1 |    20 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  2 |   TABLE ACCESS FULL          | PROJ_DETAILS_TAB |     1 |    20 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |*  3 |  FILTER                      |                  |       |       |            |          |       |       |
            |   4 |   PARTITION REFERENCE SINGLE |                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  5 |    TABLE ACCESS FULL         | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |*  6 |  FILTER                      |                  |       |       |            |          |       |       |
            |   7 |   PARTITION RANGE ALL        |                  |     2 |    34 |     4   (0)| 00:00:01 |     1 |     2 |
            |   8 |    TABLE ACCESS FULL         | PART_DEMO        |     2 |    34 |     4   (0)| 00:00:01 |     1 |     2 |
            |*  9 |   FILTER                     |                  |       |       |            |          |       |       |
            |  10 |    PARTITION REFERENCE SINGLE|                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |* 11 |     TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
            |  12 |    PARTITION REFERENCE SINGLE|                  |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
            |* 13 |     TABLE ACCESS FULL        | PROJ_DETAILS_TAB |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
            -----------------------------------------------------------------------------------------------------------------
            
            Predicate Information (identified by operation id):
            ---------------------------------------------------
            
               2 - filter("NESTED_TABLE_ID"=:B1)
               3 - filter( (SELECT "SYS_ALIAS_7"."PROJECT_HOURS" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_7" WHERE
                          "NESTED_TABLE_ID"=:B1) IS NOT NULL)
               5 - filter("NESTED_TABLE_ID"=:B1)
               6 - filter( (SELECT  (SELECT "SYS_ALIAS_6"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB"
                          "SYS_ALIAS_6" WHERE "NESTED_TABLE_ID"=:B1) FROM "TEST"."EMP_PROJ_TAB" "SYS_ALIAS_2" WHERE  (SELECT
                          "SYS_ALIAS_6"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_6" WHERE
                          "NESTED_TABLE_ID"=:B2) IS NOT NULL AND "NESTED_TABLE_ID"=:B3)='Project Manager')
               9 - filter( (SELECT "SYS_ALIAS_6"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_6"
                          WHERE "NESTED_TABLE_ID"=:B1) IS NOT NULL)
              11 - filter("NESTED_TABLE_ID"=:B1)
              13 - filter("NESTED_TABLE_ID"=:B1)
            
            
            Statistics
            ----------------------------------------------------------
                      1  recursive calls
                      0  db block gets
                    119  consistent gets
                      0  physical reads
                      0  redo size
                    416  bytes sent via SQL*Net to client
                    385  bytes received via SQL*Net from client
                      2  SQL*Net roundtrips to/from client
                      0  sorts (memory)
                      0  sorts (disk)
                      1  rows processed
            
            SQL> insert into "PART_DEMO"
              2  values
              3  (xmltype(
              4  '<EmployeeProjectInfo>
              5     <Reference>1</Reference>
              6     <Employees>
              7       <Employee id="111">
              8         <ProjectDetails>
              9           <ProjectNumber>200801010001</ProjectNumber>
             10           <ProjectHours>38</ProjectHours>
             11           <ProjectFunction>Project DBA</ProjectFunction>
             12        </ProjectDetails>
             13       </Employee>
             14     </Employees>
             15  </EmployeeProjectInfo>'));
            
            1 row created.
            
            SQL>
            SQL> select table_name,
              2         composite,
              3         partition_name,
              4         subpartition_count,
              5         num_rows,
              6         high_value
              7  from   user_tab_partitions;
            
            TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT  NUM_ROWS HIGH_VALU
            ------------------------------ --- ------------------------------ ------------------ --------- ---------
            PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P11                                0         1
            PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P21                                0         1
            EMP_PROJ_TAB                   NO  EMP_PROJ_P11                                    0         1
            EMP_PROJ_TAB                   NO  EMP_PROJ_P21                                    0         1
            PART_DEMO                      NO  P11                                             0         1 50
            PART_DEMO                      NO  P21                                             0         1 MAXVALUE
            
            6 rows selected.
            
            SQL> commit;
            
            Commit complete.
            
            SQL> select table_name,
              2         composite,
              3         partition_name,
              4         subpartition_count,
              5         num_rows,
              6         high_value
              7  from   user_tab_partitions;
            
            TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT  NUM_ROWS HIGH_VALU
            ------------------------------ --- ------------------------------ ------------------ --------- ---------
            PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P11                                0         1
            PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P21                                0         1
            EMP_PROJ_TAB                   NO  EMP_PROJ_P11                                    0         1
            EMP_PROJ_TAB                   NO  EMP_PROJ_P21                                    0         1
            PART_DEMO                      NO  P11                                             0         1 50
            PART_DEMO                      NO  P21                                             0         1 MAXVALUE
            
            6 rows selected.
            
            SQL> exec dbms_stats.gather_schema_stats('TEST')
            
            PL/SQL procedure successfully completed.
            
            SQL> select table_name,
              2         composite,
              3         partition_name,
              4         subpartition_count,
              5         num_rows,
              6         high_value
              7  from   user_tab_partitions;
            
            TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT  NUM_ROWS HIGH_VALU
            ------------------------------ --- ------------------------------ ------------------ --------- ---------
            PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P11                                0         2
            PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P21                                0         1
            EMP_PROJ_TAB                   NO  EMP_PROJ_P11                                    0         2
            EMP_PROJ_TAB                   NO  EMP_PROJ_P21                                    0         1
            PART_DEMO                      NO  P11                                             0         2 50
            PART_DEMO                      NO  P21                                             0         1 MAXVALUE
            
            6 rows selected.
            
            
            -- Cleaning up.
            
            SQL> drop table PART_DEMO;
            
            Table dropped.
            
            SQL> call DBMS_XMLSCHEMA.deleteSchema('EmpProjectInfo.xsd',4);
            
            Call completed.
            
            SQL> conn sys as sysdba
            Connected.
            
            SQL> drop user test cascade;
            
            User dropped.
            • 18. Re: XML query tuning
              919185
              Hi Marc,

              regarding your suggestion:

              XMLExists('/SearchRequest[timeLimit=("100","101","102","103","104","105")]' PASSING info)

              Do you know if there are any plans to allow this sequence of values to be softened?

              i.e.

              XMLExists('/SearchRequest[timeLimit=$seq]' PASSING info, '("100","101","102","103","104","105")' as "seq")

              Regards
              Larry
              • 19. Re: XML query tuning
                odie_63
                Hi,

                AFAIK we can't pass a sequence as an external variable, but we can do this :

                Converting an array to xmltype, then to an XQuery sequence :
                select object_value
                from temp_xml
                where xmlexists( '/SearchRequest[timeLimit=($seq/ROW/VARCHAR2)]' 
                                 passing object_value, sys_xmlgen(sys.odcivarchar2list('100','500','999','7777','30000')) as "seq" )
                ;
                Or, passing a csv string and tokenizing it into a sequence :
                select object_value
                from temp_xml
                where xmlexists( '/SearchRequest[timeLimit=ora:tokenize($seq,",")]' 
                                 passing object_value, '100,500,999,7777,30000' as "seq" )
                ;
                • 20. Re: XML query tuning
                  919185
                  Hi Marc,

                  thanks again for the prompt response.

                  Regards
                  Larry
                  1 2 Previous Next