13 Replies Latest reply: Apr 10, 2012 11:39 AM by 924923 RSS

    Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?

    924923
      We have a slow XML based query using XMLTable shown here running on 11g Rel 2 (11.2.0.1) - SUNOS based:

      select xcust.eid, xmi.ami, xmi.memberNum, xmi.accountNum
      from CROUTREACH.acme_cust cust,
         XMLTable( XMLNAMESPACES(default 'http://www.cigna.com/acme/domains/customer/customerprofile/2011/11'),
             '$doc/customerProfile'
              passing cust.object_value as "doc"
              columns 
                 eid varchar2(50) path '@eid'
         ) xcust,
         XMLTable(XMLNAMESPACES(default 'http://www.cigna.com/acme/domains/customer/customerprofile/2011/11'),
             'for $i in /customerProfile/memberInfos/memberInfo return $i'
              passing cust.object_value
              columns
                 ami varchar2(15) path 'ami',
                 memberNum varchar(20) path 'clientRelationship/memberNum',
                 accountNum varchar(20) path 'clientRelationship/accountNum'
        ) xmi
      *where xmi.accountNum = '3174016'*
      *and xmi.memberNum = '07646258301'*
      The development team has noted:

      "It's been running for a while (minutes). I'm not familiar with indexing on the XML tables, but if we can index somehow by member num/acct num (even temporarily), I think it would speed up this process tremendously."

      We on the Oracle DBA Team appended member num/acct num fields to this xmlindex upon recreating:
      CREATE INDEX ACME_CUST_XMLINDEX_IX ON ACME_CUST (OBJECT_VALUE) 
            INDEXTYPE IS XDB.XMLINDEX  PARAMETERS ('XMLTABLE ACME_CUST_IDX_TAB 
               XMLNamespaces (''http://www.cigna.com/acme/domains/commoncontact/2011/11'' as "cm",
                   default ''http://www.cigna.com/acme/domains/customer/customerprofile/2011/11''),
               ''/customerProfile'' 
              columns
                 DOB date  PATH ''personInformation/cm:birthDate'',
                 FIRSTNAME varchar2(40)    PATH ''name/cm:givenName'',
                 LASTNAME varchar2(40)  PATH ''name/cm:surName'', 
                 SSN varchar2(30) PATH ''identifiers/ssn'',
                 MEMBERINFOS XMLType path ''memberInfos/memberInfo'' VIRTUAL 
      
             XMLTable acme_cust_lev2_idx_tab 
             XMLNAMESPACES(default ''http://www.cigna.com/acme/domains/customer/customerprofile/2011/11''),
             ''/memberInfo'' passing MEMBERINFOS 
             columns 
                ami varchar2(40) PATH ''ami'',
                subscId varchar2(50) PATH ''clientRelationship/subscriberInformation/subscriberId'', 
                employeeId varchar2(50) PATH ''systemKeys/employeeId'', 
                clientId varchar2(50) PATH ''clientRelationship/clientId'',
                *accountNum varchar2(50) PATH ''clientRelationship/accountNum''*,
                *memberNum varchar2(50) PATH ''clientRelationship/memberNum'''*) PARALLEL 16;
      Unfortunately, when we run the slow XML query; a full table scan occurs on the query and the xmlindex does not get used.
      We are at a lost as to why the xmlindex is not used - before and after collecting statisitics on the base ACME_CUST table?
      Especially with highly selective predicates in the WHERE clause.


      Here is the ACME_CUST table definition:
      CREATE TABLE "CROUTREACH"."ACME_CUST" OF XMLTYPE
        (
          CONSTRAINT "ACME_CUST_ID_PK" PRIMARY KEY ("ACME_CUST_ID") USING INDEX
          PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536
          NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
          FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
          DEFAULT) TABLESPACE "ACME_DATA" ENABLE
        )
        XMLTYPE STORE AS SECUREFILE BINARY XML
        (
          TABLESPACE "ACME_DATA" ENABLE STORAGE IN ROW CHUNK 8192 CACHE READS LOGGING
          NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1
          MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
          CELL_FLASH_CACHE DEFAULT)
        )
        ALLOW NONSCHEMA ALLOW ANYSCHEMA VIRTUAL COLUMNS
        (
          "EID" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/customer/customerprofile/2011/11"; (::)                               
      /customerProfile/@eid'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(15))),
          "ACME_CUST_ID" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/customer/customerprofile/2011/11"; (::)                               
      /customerProfile/@id'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(50))),
          "CRET_DT" AS (SYS_EXTRACT_UTC(CAST(TO_TIMESTAMP_TZ(SYS_XQ_UPKXML2SQL(
          SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/customer/customerprofile/2011/11"; (::)                                                                                                       
      /customerProfile/@create_dt'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2),'SYYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM') AS TIMESTAMP
      WITH
        TIME ZONE)))
        )
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE
        (
          INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0
          FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
          CELL_FLASH_CACHE DEFAULT
        )
        TABLESPACE "ACME_DATA" ;
      Any assistance on this is much appreciated to say the least!

      Regards,
      Rick Blanchard
        • 1. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
          odie_63
          Could you post an explain plan?
          • 2. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
            924923
            Not being able to provide attachments; here's the transcription:

            OPERATION                                            OBJECT_NAME                OPTIONS                    COST
            
            SELECT STATEMENT                                                                                                     309786761
                  NESTED LOOPS                                                                                                      309786761
                        NESTED LOOPS                                                                                                173522374
                              TABLE ACCESS                      ACME_CUST                        FULL                      408973
                               XPATH EVALUATION
                                       Filter Predicates
                                             AND
                                                   CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(P1.C_01$,0,0,...
                                                   CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(P1.C_02$,0,0,...
                         XPATH EVALUATION
            Regards,
            Rick Blanchard

            Edited by: RickBlanchardSRS on Mar 22, 2012 11:42 AM
            • 3. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
              odie_63
              Is it possible you post a sample document, with dummy values if necessary, but still representative?

              I'll try to reproduce the issue, but my first guess is that the query is not "close" enough to the structure defined in the index.
              • 4. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                924923
                odie 63,

                Thank-you for your assistance. Really at wits end. Upon reviewing the 11/11 Oracle White paper on XML DB best practices you mentioned - noticing alot of XML Developer's Guide 11.2 documented functionality wasn't functioning in 11.2.0.1 and was corrected in 11.2.0.2/3.

                Here's a sample document record:
                <?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?><ns4:customerProfile status_dt="2012-02-26T18:32:07.211Z" status="VVVV" update_dt="2012-03-03T17:48:09.925Z" create_dt="2011-03-25T06:50:40.966Z" eid="99999999" id="99e9e990-99ac-99e9-a99e-99a9e99add99" xmlns:ns5="http://www.ccccc.com/bbbb/services/customer/2011/11" xmlns:ns6="http://www.ccccc.com/bbbb/domains/workforce/2010/03" xmlns:ns7="http://www.ccccc.com/bbbb/domains/entitlement/2011/11" xmlns:ns8="http://www.ccccc.com/exception/definition/schema/2010/01" xmlns:ns9="http://www.ccccc.com/bbbb/domains/utility/outcome/2010/03" xmlns:ns2="http://www.ccccc.com/bbbb/domains/commoncontact/2011/11" xmlns:ns3="http://www.ccccc.com/bbbb/domains/utility/2010/03" xmlns:ns4="http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11">
                   <ns4:identifiers>
                      <ns4:ssn>999999999</ns4:ssn>
                   </ns4:identifiers>
                   <ns4:name>
                      <ns2:givenName>AAAAAAA</ns2:givenName>
                      <ns2:surName>BBB</ns2:surName>
                   </ns4:name>
                   <ns4:personInformation>
                      <ns2:birthDate>1974-01-15</ns2:birthDate>
                      <ns2:personSex>F</ns2:personSex>
                   </ns4:personInformation>
                   <ns4:contactInformation>
                      <ns4:addresses>
                         <ns4:address auditId="BBBB-SYSTEM" collectDate="2012-03-03" addressCategory="CONFIDENTIAL" dataSource="ENTERPRISE" id="9999999999">
                            <ns2:firstLineAddress>XX</ns2:firstLineAddress>
                            <ns2:city>XX</ns2:city>
                            <ns2:state>XX</ns2:state>
                            <ns2:zipCode>00000</ns2:zipCode>
                            <ns2:description>CONFIDENTIAL</ns2:description>
                         </ns4:address>
                         <ns4:address auditId="BBBB-SYSTEM" collectDate="2012-03-03" addressCategory="HOME" dataSource="ENTERPRISE" id="9999999999">
                            <ns2:firstLineAddress>9999 MONTGOMERY RD APTX</ns2:firstLineAddress>
                            <ns2:city>CINCINNATI</ns2:city>
                            <ns2:state>OH</ns2:state>
                            <ns2:zipCode>99999</ns2:zipCode>
                            <ns2:zipCodeSuffix>1977</ns2:zipCodeSuffix>
                            <ns2:country>US</ns2:country>
                            <ns2:description>HOME</ns2:description>
                         </ns4:address>
                      </ns4:addresses>
                      <ns4:phoneNumbers>
                         <ns4:phoneNumberFull auditId="BBBB-SYSTEM" collectDate="2012-03-03" phoneNumberCategory="HOME" dataSource="ENTERPRISE" id="9999999999">
                            <ns2:phoneNumber>(000)9999999</ns2:phoneNumber>
                            <ns2:description>HOME</ns2:description>
                         </ns4:phoneNumberFull>
                         <ns4:phoneNumberFull auditId="BBBB-SYSTEM" collectDate="2012-03-03" phoneNumberCategory="UNKNOWN" dataSource="NNN" id="9999999999">
                            <ns2:phoneNumber>(513)9999999</ns2:phoneNumber>
                            <ns2:description>UNKNOWN</ns2:description>
                         </ns4:phoneNumberFull>
                      </ns4:phoneNumbers>
                      <ns4:emailAddresses>
                         <ns4:emailAddress auditId="BBBB-SYSTEM" collectDate="2012-03-03" emailCategory="UNKNOWN" dataSource="NNN" id="9999999999">ash.am@yahoo.com</ns4:emailAddress>
                      </ns4:emailAddresses>
                   </ns4:contactInformation>
                   <ns4:memberInfos>
                      <ns4:memberInfo>
                         <ns4:ami>U9999999999</ns4:ami>
                         <ns4:systemKeys>
                            <ns4:pid>999999999999999</ns4:pid>
                         </ns4:systemKeys>
                         <ns4:clientRelationship>
                            <ns4:clientId>9999999</ns4:clientId>
                            <ns4:clientName>SSSSSSSSSSS &amp; RESORTS XXXXXXXX, INC</ns4:clientName>
                            <ns4:accountNum>9999999</ns4:accountNum>
                            <ns4:memberNum>99999999999</ns4:memberNum>
                            <ns4:branchCode>9999</ns4:branchCode>
                            <ns4:subscriberInformation>
                               <ns4:subscriberId>99999999999</ns4:subscriberId>
                               <ns4:subscriberName>
                                  <ns2:givenName>AAAAAAA</ns2:givenName>
                                  <ns2:surName>BBB</ns2:surName>
                               </ns4:subscriberName>
                               <ns4:relationship>
                                  <ns4:relationshipCode>EE</ns4:relationshipCode>
                                  <ns4:relationshipDescription>Employee</ns4:relationshipDescription>
                               </ns4:relationship>
                            </ns4:subscriberInformation>
                            <ns4:eligibilityDateRange>
                               <ns3:startDate>2010-04-01</ns3:startDate>
                               <ns3:endDate>9999-12-31</ns3:endDate>
                            </ns4:eligibilityDateRange>
                            <ns4:channelCode>XXX</ns4:channelCode>
                         </ns4:clientRelationship>
                      </ns4:memberInfo>
                   </ns4:memberInfos>
                   <ns4:clientProductComponents>
                      <ns4:clientProductComponent clientId="9999999">
                         <ns4:code>GG</ns4:code>
                         <ns4:effectiveDateRange>
                            <ns3:startDate>2011-04-01</ns3:startDate>
                            <ns3:endDate>9999-12-31</ns3:endDate>
                         </ns4:effectiveDateRange>
                         <ns4:description>SSSSSSSSS PERSONAL HEALTH TEAM - A</ns4:description>
                      </ns4:clientProductComponent>
                      <ns4:clientProductComponent clientId="9999999">
                         <ns4:code>WI</ns4:code>
                         <ns4:effectiveDateRange>
                            <ns3:startDate>2009-04-01</ns3:startDate>
                            <ns3:endDate>2011-03-31</ns3:endDate>
                         </ns4:effectiveDateRange>
                         <ns4:description>ABCDE WELL INFORMED</ns4:description>
                      </ns4:clientProductComponent>
                      <ns4:clientProductComponent clientId="9999999">
                         <ns4:code>PC</ns4:code>
                         <ns4:effectiveDateRange>
                            <ns3:startDate>2009-04-01</ns3:startDate>
                            <ns3:endDate>2011-03-31</ns3:endDate>
                         </ns4:effectiveDateRange>
                         <ns4:description>PREFERENCE SENSITIVE DDDD</ns4:description>
                      </ns4:clientProductComponent>
                      <ns4:clientProductComponent clientId="9999999">
                         <ns4:code>QU</ns4:code>
                         <ns4:effectiveDateRange>
                            <ns3:startDate>2011-04-01</ns3:startDate>
                            <ns3:endDate>9999-12-31</ns3:endDate>
                         </ns4:effectiveDateRange>
                         <ns4:description>QUIT TODAY</ns4:description>
                      </ns4:clientProductComponent>
                      <ns4:clientProductComponent clientId="9999999">
                         <ns4:code>HW</ns4:code>
                         <ns4:effectiveDateRange>
                            <ns3:startDate>2011-04-01</ns3:startDate>
                            <ns3:endDate>9999-12-31</ns3:endDate>
                         </ns4:effectiveDateRange>
                         <ns4:description>HEALTHY ZZZZZ TO WEIGHT LOSS</ns4:description>
                      </ns4:clientProductComponent>
                      <ns4:clientProductComponent clientId="9999999">
                         <ns4:code>FF</ns4:code>
                         <ns4:effectiveDateRange>
                            <ns3:startDate>2011-04-01</ns3:startDate>
                            <ns3:endDate>9999-12-31</ns3:endDate>
                         </ns4:effectiveDateRange>
                         <ns4:description>DDDDDDDDD AND RESILIENCE</ns4:description>
                      </ns4:clientProductComponent>
                   </ns4:clientProductComponents>
                   <ns4:preferences>
                      <ns4:contactInfoPreferences>
                         <ns4:preferredAddressMarker auditId="BBBB-SYSTEM" collectDate="2012-03-03" markedId="9999999999"/>
                         <ns4:preferredPhoneMarker auditId="BBBB-SYSTEM" collectDate="2012-03-03" markedId="9999999999"/>
                         <ns4:preferredEmailMarker auditId="BBBB-SYSTEM" collectDate="2012-03-03" markedId="9999999999"/>
                      </ns4:contactInfoPreferences>
                      <ns4:richestMembership productCode="TTTT" memberNum="99999999999" accountNum="9999999" clientId="9999999"/>
                   </ns4:preferences>
                   <ns4:privacy>
                      <ns4:optOuts></ns4:optOuts>
                   </ns4:privacy>
                </ns4:customerProfile>
                Regards,
                Rick Blanchard
                • 5. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                  odie_63
                  Thanks for the sample.

                  I've inserted 20000 instances of it in the table and updated 10 rows with a specific memberNum and accountNum.

                  Test #1 : simple XMLTable expression, no index of any kind on the table.
                  Also note that, unless you need other columns, you don't have to define the first XMLTable in your original query, the eid attribute can be retrieved through the corresponding virtual column.
                  SQL> set autotrace on explain
                  SQL> set timing on
                  SQL> set lines 120
                  SQL> select xmi.*
                    2  from acme_cust cust
                    3     , XMLTable(
                    4         XMLNamespaces(default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11')
                    5       , '/customerProfile/memberInfos/memberInfo'
                    6         passing cust.object_value
                    7         columns
                    8           ami        varchar2(40) path 'ami'
                    9         , memberNum  varchar(50)  path 'clientRelationship/memberNum'
                   10         , accountNum varchar(50)  path 'clientRelationship/accountNum'
                   11       ) xmi
                   12  where xmi.accountNum = '3174016'
                   13  and xmi.memberNum = '07646258301'
                   14  ;
                  
                  AMI             MEMBERNUM       ACCOUNTNUM
                  --------------- --------------- ---------------
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  U9999999999     07646258301     3174016
                  
                  10 rows selected.
                  
                  Elapsed: 00:00:01.71
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 424464371
                  
                  --------------------------------------------------------------------------------
                  | Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                  --------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT   |           | 16336 |    29M|   547K  (1)| 01:49:32 |
                  |   1 |  NESTED LOOPS      |           | 16336 |    29M|   547K  (1)| 01:49:32 |
                  |   2 |   TABLE ACCESS FULL| ACME_CUST | 20000 |    36M|  1810   (1)| 00:00:22 |
                  |*  3 |   XPATH EVALUATION |           |       |       |            |          |
                  --------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     3 - filter(CAST("P"."C_01$" AS varchar(50)  )='3174016' AND
                                CAST("P"."C_02$" AS varchar(50)  )='07646258301')
                  The 10 rows are fetched in less than 2 seconds. Not bad.


                  Test #2 : using a structured index

                  When you create a structured XML index, you must use the same structure in the query that's meant to use the index.
                  For example, in your first attempt, the first level XMLTable retrieves the eid attribute, but that column is not defined in the index, consequently the query is not "recognized" as a candidate for a rewrite using the index.

                  However, if we use a column that's been defined in the index (for ex. DOB) :
                  SQL> select x.dob, xmi.*
                    2  from acme_cust cust
                    3     , XMLTable(
                    4         XMLNamespaces( 'http://www.ccccc.com/bbbb/domains/commoncontact/2011/11' as "cm"
                    5                      , default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11' )
                    6       , '/customerProfile'
                    7         passing object_value
                    8         columns
                    9           DOB         date    PATH 'personInformation/cm:birthDate'
                   10         , MEMBERINFOS XMLType path 'memberInfos/memberInfo'
                   11       ) x
                   12     , XMLTable(
                   13         XMLNAMESPACES(default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11')
                   14       , '/memberInfo'
                   15         passing x.MEMBERINFOS
                   16         columns
                   17           ami        varchar2(15) path 'ami'
                   18         , memberNum  varchar(50)  path 'clientRelationship/memberNum'
                   19         , accountNum varchar(50)  path 'clientRelationship/accountNum'
                   20       ) xmi
                   21  where xmi.accountNum = '3174016'
                   22  and xmi.memberNum = '07646258301'
                   23  ;
                  
                  DOB       AMI             MEMBERNUM       ACCOUNTNUM
                  --------- --------------- --------------- ---------------
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  
                  10 rows selected.
                  
                  Elapsed: 00:00:00.04
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 1964615435
                  
                  -------------------------------------------------------------------------------------------------------
                  | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                  -------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT             |                        |     1 |   100 |   137   (0)| 00:00:02 |
                  |   1 |  NESTED LOOPS                |                        |       |       |            |          |
                  |   2 |   NESTED LOOPS               |                        |     1 |   100 |   137   (0)| 00:00:02 |
                  |*  3 |    TABLE ACCESS FULL         | ACME_CUST_LEV2_IDX_TAB |     1 |    62 |   136   (0)| 00:00:02 |
                  |*  4 |    INDEX RANGE SCAN          | SYS80007_80008_KEY_IDX |     1 |       |     0   (0)| 00:00:01 |
                  |   5 |   TABLE ACCESS BY INDEX ROWID| ACME_CUST_IDX_TAB      |     1 |    38 |     1   (0)| 00:00:01 |
                  -------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     3 - filter("SYS_SXI_3"."ACCOUNTNUM"='3174016' AND "SYS_SXI_3"."MEMBERNUM"='07646258301')
                     4 - access("SYS_SXI_2"."KEY"="SYS_SXI_3"."PKEY")
                  Now the underlying storage structure is used.


                  Test #3 : creating a secondary index

                  The performance can be further improved by adding a secondary index on the child table (ACME_CUST_LEV2_IDX_TAB).
                  In the following example, I'm just including MEMBERNUM column but you can add ACCOUNTNUM too if you think it's relevant and will increase selectivity.
                  SQL> CREATE INDEX acme_cust_membernum_ix ON acme_cust_lev2_idx_tab (membernum);
                  
                  Index created.
                  
                  Elapsed: 00:00:00.12
                  SQL> select x.dob, xmi.*
                    2  from acme_cust cust
                    3     , XMLTable(
                    4         XMLNamespaces( 'http://www.ccccc.com/bbbb/domains/commoncontact/2011/11' as "cm"
                    5                      , default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11' )
                    6       , '/customerProfile'
                    7         passing object_value
                    8         columns
                    9           DOB         date    PATH 'personInformation/cm:birthDate'
                   10         , MEMBERINFOS XMLType path 'memberInfos/memberInfo'
                   11       ) x
                   12     , XMLTable(
                   13         XMLNAMESPACES(default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11')
                   14       , '/memberInfo'
                   15         passing x.MEMBERINFOS
                   16         columns
                   17           ami        varchar2(15) path 'ami'
                   18         , memberNum  varchar(50)  path 'clientRelationship/memberNum'
                   19         , accountNum varchar(50)  path 'clientRelationship/accountNum'
                   20       ) xmi
                   21  where xmi.accountNum = '3174016'
                   22  and xmi.memberNum = '07646258301'
                   23  ;
                  
                  DOB       AMI             MEMBERNUM       ACCOUNTNUM
                  --------- --------------- --------------- ---------------
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  
                  10 rows selected.
                  
                  Elapsed: 00:00:00.11
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 3482473246
                  
                  --------------------------------------------------------------------------------------------------------
                  | Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                  --------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT              |                        |     1 |   100 |     3   (0)| 00:00:01 |
                  |   1 |  NESTED LOOPS                 |                        |       |       |            |          |
                  |   2 |   NESTED LOOPS                |                        |     1 |   100 |     3   (0)| 00:00:01 |
                  |*  3 |    TABLE ACCESS BY INDEX ROWID| ACME_CUST_LEV2_IDX_TAB |     1 |    62 |     2   (0)| 00:00:01 |
                  |*  4 |     INDEX RANGE SCAN          | ACME_CUST_MEMBERNUM_IX |     1 |       |     1   (0)| 00:00:01 |
                  |*  5 |    INDEX RANGE SCAN           | SYS80007_80008_KEY_IDX |     1 |       |     0   (0)| 00:00:01 |
                  |   6 |   TABLE ACCESS BY INDEX ROWID | ACME_CUST_IDX_TAB      |     1 |    38 |     1   (0)| 00:00:01 |
                  --------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     3 - filter("SYS_SXI_3"."ACCOUNTNUM"='3174016')
                     4 - access("SYS_SXI_3"."MEMBERNUM"='07646258301')
                     5 - access("SYS_SXI_2"."KEY"="SYS_SXI_3"."PKEY")
                  The new index is used to access the child table.

                  The caveat about matching index and query structure is actually two-fold : also take care of the datatypes.
                  Let's see what happens if we change the datatype of the projected column MEMBERNUM in the query, say from VARCHAR2(50) to VARCHAR2(20) :
                  SQL> select x.dob, xmi.*
                    2  from acme_cust cust
                    3     , XMLTable(
                    4         XMLNamespaces( 'http://www.ccccc.com/bbbb/domains/commoncontact/2011/11' as "cm"
                    5                      , default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11' )
                    6       , '/customerProfile'
                    7         passing object_value
                    8         columns
                    9           DOB         date    PATH 'personInformation/cm:birthDate'
                   10         , MEMBERINFOS XMLType path 'memberInfos/memberInfo'
                   11       ) x
                   12     , XMLTable(
                   13         XMLNAMESPACES(default 'http://www.ccccc.com/bbbb/domains/customer/customerprofile/2011/11')
                   14       , '/memberInfo'
                   15         passing x.MEMBERINFOS
                   16         columns
                   17           ami        varchar2(15) path 'ami'
                   18         , memberNum  varchar(20)  path 'clientRelationship/memberNum'
                   19         , accountNum varchar(50)  path 'clientRelationship/accountNum'
                   20       ) xmi
                   21  where xmi.accountNum = '3174016'
                   22  and xmi.memberNum = '07646258301'
                   23  ;
                  
                  DOB       AMI             MEMBERNUM       ACCOUNTNUM
                  --------- --------------- --------------- ---------------
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  15-JAN-74 U9999999999     07646258301     3174016
                  
                  10 rows selected.
                  
                  Elapsed: 00:00:00.09
                  
                  Execution Plan
                  ----------------------------------------------------------
                  Plan hash value: 1964615435
                  
                  -------------------------------------------------------------------------------------------------------
                  | Id  | Operation                    | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
                  -------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT             |                        |     1 |   100 |   137   (0)| 00:00:02 |
                  |   1 |  NESTED LOOPS                |                        |       |       |            |          |
                  |   2 |   NESTED LOOPS               |                        |     1 |   100 |   137   (0)| 00:00:02 |
                  |*  3 |    TABLE ACCESS FULL         | ACME_CUST_LEV2_IDX_TAB |     1 |    62 |   136   (0)| 00:00:02 |
                  |*  4 |    INDEX RANGE SCAN          | SYS80007_80008_KEY_IDX |     1 |       |     0   (0)| 00:00:01 |
                  |   5 |   TABLE ACCESS BY INDEX ROWID| ACME_CUST_IDX_TAB      |     1 |    38 |     1   (0)| 00:00:01 |
                  -------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     3 - filter("SYS_SXI_3"."ACCOUNTNUM"='3174016' AND CAST("SYS_SXI_3"."MEMBERNUM" AS
                                varchar(20)  )='07646258301')
                     4 - access("SYS_SXI_2"."KEY"="SYS_SXI_3"."PKEY")
                  The secondary index is not used anymore.
                  • 6. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                    924923
                    odie 63,

                    Just reviewed your explanations and test cases. Very impressed! The Oracle XML DB Developer's Guide 11g Release 2 (11.2), E23094-01 June 2011 doesn't mention these caveats/restrictions. Would've been nice if the documentation alluded to these caveats in some form...

                    I'll experiment with the two caveats you mention - suspect these two caveats are why sometimes my xmlindex and 2ndar index would work and other times not - BRAVO! Been dogged with this for awhile!

                    To be clear, the two caveats:

                    1. Make sure that ALL the select list columns get defined in the XMLIndex and strict datatype matching - including the same max length for the VARCHAR2 data type.

                    2. Ensure the same max length is used for variable length strings in the XMLIndex and query structure.

                    odie; thank-you again for expending your time in your depicitions and explanations on this issue.

                    Regards,
                    Rick Blanchard

                    Edited by: RickBlanchardSRS on Apr 5, 2012 2:05 PM
                    • 7. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                      924923
                      Following up on odie 63's suggestions

                      Per below;

                      "I'll experiment with the two caveats you mention - suspect these two caveats are why sometimes my xmlindex and 2ndar index would work and other times not - BRAVO! Been dogged with this for awhile!

                      To be clear, the two caveats:

                      1. Make sure that ALL the select list columns get defined in the XMLIndex and strict datatype matching - including the same max length for the VARCHAR2 data type.

                      2. Ensure the same max length is used for variable length strings in the XMLIndex and 2ndary index definitions."

                      Upon making sure all select list columns got defined in the XMLIndex definition, the xmlindex started being used in the explain plan.
                      Upon ensuring the varchar2 datatypes and sizes were the same in the xmlindex and query structure; proceeded to create 2ndary single column indexes for accountNum and memberNum and 1 composite index using accountNum,memeberNum in this order.

                      italicsTo my suprise NO 2ndary index was used...

                      Why is this? italics


                      Here' are supporting information:

                      1. Select statement
                       select xcust.eid, xmi.ami, xmi.memberNum, xmi.accountNum
                      from acme_cust cust,
                      XMLTable(
                      XMLNAMESPACES(default 'http://www.cigna.com/acme/domains/customer/customerprofile/2011/11'),
                      '$doc/customerProfile'
                      passing cust.object_value as "doc"
                      columns eid varchar2(50) path '@eid'
                      ) xcust,
                      XMLTable(
                      XMLNAMESPACES(default 'http://www.cigna.com/acme/domains/customer/customerprofile/2011/11'),
                      'for $i in /customerProfile/memberInfos/memberInfo return $i'
                      passing cust.object_value
                      columns
                      ami varchar2(15) path 'ami',
                      memberNum varchar(20) path 'clientRelationship/memberNum',
                      accountNum varchar(20) path 'clientRelationship/accountNum'
                      ) xmi
                      where xmi.accountNum = '3174333'
                      and xmi.memberNum = '07646258333'  
                      2. The xmlindex create statement:
                       
                      INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS('XMLTABLE ACME_CUST_IDX_TABL XMLNamespaces(''http://www.cigna.com/acme/domains/commoncontact/2011/11'' as "cm",default ''http://www.cigna.com/acme/domains/customer/customerprofile/2011/11''),''/customerProfile'' 
                      columns eid varchar2(50) path ''@eid'',
                      DOB date PATH ''personInformation/cm:birthDate'',FIRSTNAME varchar2(40) PATH ''name/cm:givenName'',LASTNAME varchar2(40) PATH ''name/cm:surName'', SSN varchar2(30) PATH ''identifiers/ssn'', MEMBERINFOS XMLType path ''memberInfos/memberInfo'' VIRTUAL 
                      XMLTable acme_cust_lev2_idx_tabl XMLNAMESPACES(default ''http://www.cigna.com/acme/domains/customer/customerprofile/2011/11''),''/memberInfo'' passing MEMBERINFOS columns ami varchar2(40) PATH ''ami'', subscId varchar2(50) PATH ''clientRelationship/subscriberInformation/subscriberId'', employeeId varchar2(50) PATH ''systemKeys/employeeId'', clientId varchar2(50) PATH ''clientRelationship/clientId'', accountNum varchar2(20) PATH ''clientRelationship/accountNum'', memberNum varchar2(20) PATH ''clientRelationship/memberNum''')
                      ; 
                      3. The 2ndary index creation statements:
                      CREATE INDEX acme_cust_xmlindex2_accountNum ON acme_cust_lev2_idx_tabl (accountNum);
                      CREATE INDEX acme_cust_xmlindex2_memberNum ON acme_cust_lev2_idx_tabl (memberNum); 
                      4. The explain plan
                       SELECT STATEMENT
                                        NESTED LOOPS
                                             MERGE JOIN
                                                   TABLE ACCESS                              ACME_CUST_IDX_TABL   BY INDEX ID
                                                    SORT                                                                              JOIN
                                                        Access Predicates
                                                        Filter Predicates
                                                        TABLE ACCESS                        ACME_CUST                    FULL
                                             XPATHEVALUATION
                                                  Filter Predicates
                                                      AND
                                                                    .... varchar(20)) = '3174333'
                                                                    .... varchar(20)) = '7646258333' 
                      5. Am doing this in a development environment with a small number of rows for ACME_CUST.
                      Though we're planning on doing this tonite with full up volume for ACME_CUST - to see if this makes a difference.

                      Any suggestions much appreciated...
                      Rick Blanchard
                      • 9. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                        odie_63
                        Rick,

                        You're still not matching query and index structure.

                        In the XML index definition, the 2nd XMLTABLE is correlated to the 1st via the PASSING clause, whereas in the query you're doing a cartesian join by starting again from the document node.


                        A little disgression about posting explain plan...
                        Where are you getting them?

                        To post a readable plan here, I think it's better to use SQL*Plus' autotrace feature, for example :
                        SET AUTOTRACE ON EXPLAIN
                        or the EXPLAIN statement :
                        EXPLAIN PLAN FOR <query>;
                        immediately followed by :
                        select * from table(dbms_xplan.display(format => 'TYPICAL'));
                        • 10. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                          924923
                          Marco,

                          I had the stats refreshed last night. Still the same execution plan - not using the indexes.
                          • 11. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                            924923
                            odie 63,

                            Am using sql developer and then transcribing the results, as can't attach files in posts...

                            I would've used SQL*Plus if the explain plan results got larger or more complex.

                            Interesting observation you made...

                            In your statement: "In the XML index definition, the 2nd XMLTABLE is correlated to the 1st via the PASSING clause, whereas in the query you're doing a cartesian join by starting again from the document node."

                            The XMLIndex is getting used at least in our small developement environment; just the 2ndar index isn't getting used.

                            How would you suggest cleaning this situation?

                            Would cleaning up the query to avoid the cartesioan join or somehow introducing a correlation in the query be tenable? If so, how would i apporach doing this?

                            Regards,
                            Rick Blanchard
                            • 12. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                              odie_63
                              Would cleaning up the query to avoid the cartesioan join or somehow introducing a correlation in the query be tenable?
                              Well, first of all, is the cartesian join done on purpose?
                              If so, how would i apporach doing this?
                              Where's the problem?
                              You've already done it in the index definition. Use the same in the query :
                              SELECT xcust.eid, xmi.ami, xmi.memberNum, xmi.accountNum
                              FROM acme_cust cust,
                                   XMLTable(
                                     XMLNAMESPACES(default 'http://www.cigna.com/acme/domains/customer/customerprofile/2011/11'),
                                     '$doc/customerProfile'
                                     passing cust.object_value as "doc"
                                     columns eid         varchar2(50) path '@eid'
                                             MEMBERINFOS XMLType      path 'memberInfos/memberInfo'
                                   ) xcust,
                                   XMLTable(
                                     XMLNAMESPACES(default 'http://www.cigna.com/acme/domains/customer/customerprofile/2011/11'),
                                     '/memberInfo'
                                     passing xcust.MEMBERINFOS
                                     columns
                                       ami        varchar2(15) path 'ami',
                                       memberNum  varchar(20)  path 'clientRelationship/memberNum',
                                       accountNum varchar(20)  path 'clientRelationship/accountNum'
                                   ) xmi
                              WHERE xmi.accountNum = '3174333'
                              AND xmi.memberNum = '07646258333'
                              BTW, don't multiply the indexes unnecessarily.
                              For example, you said :
                              proceeded to create 2ndary single column indexes for accountNum and memberNum and 1 composite index using accountNum,memeberNum in this order.
                              If you create a composite index, you don't need another one on the leading column (in this case you don't need the index on accountNum).
                              • 13. Re: Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?
                                924923
                                odie 63

                                Thank-you for the follow-up. In our development environment, set up the correlated query and the optimizer picked up the xmlindex and 2ndary index! We have master - detail relationship whereas an employee id (eid attribute in the root element) can havew multiple member numbers and account numbers.

                                The next step will be to validate the transformed query is correct and run this query in our database performance and volume environment with full up gigabyte size ACME_CUST XMLType table.

                                Didn't realize the query also had to reflect the correlated nature of the XMLIndex so the optimizer would recognize it. If this query is semantically equivalent to the original query end up avoiding Full Table Scans - Like that.

                                This afternoon, will try your advice on getting the the XMLIndex and 2ndary index to be used for a simpler query. Will start another thread if unable to get the xmlindex/2ndary index used.

                                Thank-you again for sharing your xml experience and insight on this thread.

                                Rick Blanchard