1 2 Previous Next 29 Replies Latest reply: Mar 31, 2011 5:27 PM by Marco Gralike RSS

    XMLIndex is not getting used

    644524
      Hi,
      We created XMLIndex using below DDL:

      SQL> CREATE INDEX TNMAB_AGREEMENT_XML_XIDX1 ON TNMAB_AGREEMENT_XML_BINARY(AGREEMENT_XML)
      2 INDEXTYPE IS XDB.XMLINDEX
      3 PARAMETERS (
      4 'XMLTABLE IXTAB XMLNAMESPACES(DEFAULT ''http://com.oocl.schema.tnm.agreementbuilder''),
      5 ''/Agreement''
      6 COLUMNS
      7 LAST_UPDATED_BY VARCHAR2(10) PATH ''LastUpdatedBy'',
      8 LAST_UPDATED TIMESTAMP path ''LastUpdated'',
      9 CREATION_DATE TIMESTAMP path ''Created'',
      10 SP XMLType PATH ''ShippingPartyGroups/ShippingParties'' VIRTUAL
      11 XMLTABLE SP_TAB
      12      XMLNAMESPACES(DEFAULT ''http://com.oocl.schema.tnm.agreementbuilder''),
      13 ''/ShippingParties'' PASSING SP
      14      COLUMNS
      15 SAP_ID VARCHAR2(10) PATH ''CustomerHolder/SAP_ID'',
      16 NC XMLType PATH ''NamedCustomerGroups/NamedCustomer'' VIRTUAL
      17 XMLTABLE NC_TAB
      18      XMLNAMESPACES(DEFAULT ''http://com.oocl.schema.tnm.agreementbuilder''),
      19 ''/NamedCustomer'' PASSING NC
      20      COLUMNS
      21      SW XMLType PATH ''SAP_IDs/StringWrappers'' VIRTUAL
      22 XMLTABLE SW_TAB
      23 XMLNAMESPACES(DEFAULT ''http://com.oocl.schema.tnm.agreementbuilder''),
      24 ''/StringWrappers'' PASSING SW
      25      COLUMNS
      26      VALUE VARCHAR2(15) PATH ''Value'''
      27 );

      Index created

      Executed in 17.5 seconds

      Tried running query with XMLExists

      SELECT agreements.agreement_xml.getClobVal()
      FROM tnmab_agreement_xml_binary agreements
      WHERE
      XMLExists('declare default element namespace "http://com.oocl.schema.tnm.agreementbuilder"; $XML/Agreement/ShippingPartyGroups/ShippingParties/CustomerHolder/SAP_ID["$ARG"]'
      passing agreements.AGREEMENT_XML as "XML", '5000003001' as "ARG")
      OR
      XMLExists('declare default element namespace "http://com.oocl.schema.tnm.agreementbuilder"; $XML/Agreement/NamedCustomerGroups/NamedCustomer/SAP_IDs/StringWrappers/Value["$ARG"]'
      passing agreements.AGREEMENT_XML as "XML", '5000003001' as "ARG")
      /

      It is still giving Full Table Scan and XMLIndex defined above is not getting used.
      Please see EXPLAIN PLAN below:

      OPERATION OBJECT_NAME OPTIONS COST
      SELECT STATEMENT 18
      FILTER
      Fiter Predicates
      OR
      EXISTS (SELECT 0 FROM XPATHTABLE('/oraxq_defpfx:Agreement/oraxq_defpfx:ShippingPartyGroups/oraxq_defpfx:ShippingParties/oraxq_defpfx:CustomerHolder/oraxq_defpfx:SAP_ID' PASSING :B1 COLUMNS C_00$ XMLTYPE PATH '.') P)
      EXISTS (SELECT 0 FROM XPATHTABLE('/oraxq_defpfx:Agreement/oraxq_defpfx:NamedCustomerGroups/oraxq_defpfx:NamedCustomer/oraxq_defpfx:SAP_IDs/oraxq_defpfx:StringWrappers/oraxq_defpfx:Value' PASSING :B2 COLUMNS C_00$ XMLTYPE PATH '.') P1)

      TABLE ACCESS
      TNMAB_AGREEMENT_XML_BINARY FULL 18

      XPATH EVALUATION
      XPATH EVALUATION


      Is there something wrong in XMLIndex definition

      Sample XML doc:

      <?xml version="1.0" encoding="UTF-8"?><!DOCTYPE s:sequence []>
      <s:sequence xmlns:s="http://www.stylusstudio.com/xquery">
      <AGREEMENT_XML>
      <Agreement xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://com.oocl.schema.tnm.agreementbuilder">
      <ObjectID>100000000001130</ObjectID>
      <Identifier>00000023</Identifier>
      <Version>0</Version>
      <IsLatestVersion>true</IsLatestVersion>
      <Status>Incomplete</Status>
      <Flags>1</Flags>
      <AgreementType>AB</AgreementType>
      <ValidityDays>30</ValidityDays>
      <SalesOfficeCodes>
      <StringWrappers>
      <Value>ZHA</Value>
      </StringWrappers>
      </SalesOfficeCodes>
      <ShippingPartyGroups>
      <Name>Default</Name>
      <ShippingParties>
      <CustomerHolder>
      <SAP_ID>5000003001</SAP_ID>
      </CustomerHolder>
      <ShippingRoleGroupID>32</ShippingRoleGroupID>
      </ShippingParties>
      </ShippingPartyGroups>
      <CustomerContactGroups>
      <Name>Default</Name>
      </CustomerContactGroups>
      <SecondaryContactID>LIUJO3</SecondaryContactID>
      <FormatSettings>
      <TemplateName>Standard</TemplateName>
      <ShowSurcharges>All</ShowSurcharges>
      <AdditionalRemarks>
      </AdditionalRemarks>
      <AdditionalNotes>
      </AdditionalNotes>
      </FormatSettings>
      <Created>2010-06-28T00:00:00.000000</Created>
      <CreatedBy>LIUJO3</CreatedBy>
      <LastUpdated>2010-06-27T22:29:45.598000</LastUpdated>
      <LastUpdatedBy>LIUJO3</LastUpdatedBy>
      <NextRateID>2</NextRateID>
      <NextVolumeID>1</NextVolumeID>
      <NextSentOfferID>10001</NextSentOfferID>
      <LockStamp xmlns="http://com.oocl.schema.tnm.agreementbuilder">20101022150028.663</LockStamp>
      </Agreement>
      </AGREEMENT_XML>

      Thanks
      Auro
        • 1. Re: XMLIndex is not getting used
          Marco Gralike
          Did the XMLTABLE syntax you based your XMLINDEX upon return any rows...?
          • 2. Re: XMLIndex is not getting used
            Marco Gralike
            Did you create any statistics on the tables involved...?
            • 3. Re: XMLIndex is not getting used
              644524
              Hi,
              We had gathered DB Stats on the main Binary XML Table (TNMAB_AGREEMENT_XML_BINARY )

              Do we also need to gather DB Stats on the XMLIndex Table structure.
              Please let me know.

              Tried to execute following to list XMLTable related to XMLIndex, it gives error:

              SQL> desc IXTAB
              Name Type Nullable Default Comments
              --------------- ------------ -------- ------- --------
              KEY RAW(1000)
              RID ROWID Y
              LAST_UPDATED_BY VARCHAR2(10) Y
              LAST_UPDATED TIMESTAMP(6) Y
              CREATION_DATE TIMESTAMP(6) Y

              SQL> select count(*) from IXTAB;

              select count(*) from IXTAB

              ORA-30967: operation directly on the Path Table is disallowed

              SQL> select * from IXTAB;

              select * from IXTAB

              ORA-30967: operation directly on the Path Table is disallowed

              SQL> desc SP_TAB
              Name Type Nullable Default Comments
              ------ ------------ -------- ------- --------
              KEY RAW(1000)
              RID ROWID Y
              PKEY RAW(1000)
              SAP_ID VARCHAR2(10) Y

              SQL> select * from SP_TAB;

              select * from SP_TAB

              ORA-30967: operation directly on the Path Table is disallowed

              SQL>


              Please help

              Thanks
              Auro
              • 4. Re: XMLIndex is not getting used
                644524
                Since, XMLIndex Creation itself went thru' fine, thought it should get picked up anyway.

                Is there anything wrong in my SQL Query (with XMLExists). Had based it on example from
                Oracle XML DB Developer Guide

                Thanks
                Auro
                • 5. Re: XMLIndex is not getting used
                  Marco Gralike
                  I guess the error is self-explaintory

                  ORA-30967: operation directly on the Path Table is disallowed

                  You are not allowed to do that. ;-)

                  If you create an XMLINDEX based on a query that comes up with no results, like if you forget a mandatory namespace reference, than the PATH or CONTENT TABLES which are used by the XMLINDEX, end up empty with no values and therefore will not get used by the optimizer and or return you the wrong values... You should always create statistics otherwise the optimizer will ESTIMATE your costs regarding the query and this may be bot the correct execution path you need...
                  • 6. Re: XMLIndex is not getting used
                    644524
                    Hi,
                    Based on XMLIndex created, trying to query the different inner/nested XMLType and get below error:

                    SELECT XTAB.SP.getClobVal()
                    FROM tnmab_agreement_xml_binary agmt,
                    XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),
                    '/Agreement'
                    PASSING agmt.AGREEMENT_XML
                    COLUMNS
                    LAST_UPDATED_BY VARCHAR2(10) PATH 'LastUpdatedBy',
                    SP XMLType PATH 'ShippingPartyGroups/ShippingParties'
                    -- XMLTable('/ShippingParties' PASSING SPTAB.SP
                    -- COLUMNS SAP_ID VARCHAR2(10) PATH 'CustomerHolder/SAP_ID') YTAB
                    ) XTAB

                    ORA-06553: PLS-306: wrong number or types of arguments in call to 'GETCLOBVAL'
                    06553. 00000 - "PLS-%s: %s"
                    *Cause:   
                    *Action:
                    Error at Line: 4 Column: 7

                    Trying to list nested XMLType (SP ) using getClobVal() just as we would query XMLType column in
                    any Table

                    Any help is greatly appreciated.

                    Thanks
                    Auro
                    • 7. Re: XMLIndex is not getting used
                      Marco Gralike
                      You never answered the following...
                      Did the XMLTABLE syntax you based your XMLINDEX upon return any rows...?
                      • 8. Re: XMLIndex is not getting used
                        Marco Gralike
                        In principle it should work just like the following...
                        SQL> select xmltype(cursor(select * from dual)).GetClobVal() as "Result" from dual;
                        
                        Result
                        -----------------------------------------
                        <?xml version="1.0"?>
                        <ROWSET>
                         <ROW>
                          <DUMMY>X</DUMMY>
                         </ROW>
                        </ROWSET>
                        Try again using
                        SELECT XTAB.SP.getClobVal() as "SP"
                          FROM tnmab_agreement_xml_binary agmt
                             , XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),
                                        '/Agreement'
                                        PASSING agmt.AGREEMENT_XML
                                        COLUMNS
                                          SP XMLType PATH 'ShippingPartyGroups/ShippingParties'
                                       ) XTAB
                        
                        or
                        
                        SELECT XTAB.SP as "SP"
                          FROM tnmab_agreement_xml_binary agmt
                             , XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),
                                        '/Agreement'
                                        PASSING agmt.AGREEMENT_XML
                                        COLUMNS
                                          SP CLOB PATH 'ShippingPartyGroups/ShippingParties'
                                       ) XTAB
                        
                        or
                        
                        SELECT xmltype(XTAB.SP).GetClobVal() as "SP"
                          FROM tnmab_agreement_xml_binary agmt
                             , XMLTable(XMLNameSpaces(default 'http://com.oocl.schema.tnm.agreementbuilder'),
                                        '/Agreement'
                                        PASSING agmt.AGREEMENT_XML
                                        COLUMNS
                                          SP XMLType PATH 'ShippingPartyGroups/ShippingParties'
                                       ) XTAB
                                       
                        Edited by: Marco Gralike on Mar 22, 2011 6:41 PM
                        • 9. Re: XMLIndex is not getting used
                          644524
                          You never answered the following...
                          Did the XMLTABLE syntax you based your XMLINDEX upon return any rows...?
                          Hi Marco,

                          In fact I was trying to query the XMLTable structure when I got this error Today.

                          Will try few syntax options given by you and let you know


                          Thanks a lot for the help

                          Auro
                          • 10. Re: XMLIndex is not getting used
                            Marco Gralike
                            Odie/Marc has shown you an example a week or so ago that created an XMLINDEX like the following

                            ORA-19276: XPST0005  Error creating XMLIndex on Nested Collection
                            CREATE INDEX TNMAB_AGREEMENT_XML_XIDX1 ON TNMAB_AGREEMENT_XML_BINARY(AGREEMENT_XML) 
                            INDEXTYPE IS XDB.XMLINDEX
                            PARAMETERS (
                            'XMLTABLE IXTAB 
                             XMLNAMESPACES(DEFAULT ''http://com.oocl.schema.tnm.agreementbuilder''),
                             ''/Agreement''
                             COLUMNS
                               AGREEMENT_ID VARCHAR2(20) PATH ''Identifier'',
                               LAST_UPDATED_BY VARCHAR2(10) PATH ''LastUpdatedBy'',
                               LAST_UPDATED TIMESTAMP path ''LastUpdated'',
                               CREATION_DATE TIMESTAMP path ''Created'',
                               STRING_WRAPPERS XMLType PATH ''SalesOfficeCodes/StringWrappers'' VIRTUAL
                             XMLTABLE STRING_WRAPPER_TAB 
                             XMLNAMESPACES(DEFAULT ''http://com.oocl.schema.tnm.agreementbuilder''),
                             ''/StringWrappers'' PASSING STRING_WRAPPERS
                             COLUMNS 
                               VALUE VARCHAR2(15) PATH ''Value'''
                            )
                            ;
                            First of all, I want to be sure that you used every time two single quotes. I checked your posts but am not yet really sure if you not by accident used a double quote (") instead of 2 single quotes ( ' ' ) in your create XMLINDEX syntax. The PARAMETERS section is actually a STRING and therefore single quotes in the syntax have to be ' ' to result in a ' in the actual end result....

                            Second I tried to reverse engineer your create XMLINDEX statement into the actual XMLTABLE construct that MUST result in data. This is the first step in creating an XMLINDEX that can be used in your queries. As said, if not, than this will result in EMPTY content tables and the optimizer will do a full scan and/or will come up with the WRONG execution path...
                            -- something like the following...
                            
                            select x1.*, x2.*, x3.*, x4.*
                            -- or use the correct column names – probably better - bit lazy here and went for the shortcut
                            FROM 
                              tnmab_agreement_xml_binary XDATA
                            , XMLTABLE  (XMLNAMESPACES(DEFAULT 'http://com.oocl.schema.tnm.agreementbuilder'),
                                             '/Agreement'
                                             PASSING xdata.object_value
                                             COLUMNS
                                                LAST_UPDATED_BY VARCHAR2(10) PATH  'LastUpdatedBy',
                                                LAST_UPDATED      TIMESTAMP      path 'LastUpdated',
                                                CREATION_DATE    TIMESTAMP      path 'Created',
                                                SP                         XMLType   PATH 'ShippingPartyGroups/ShippingParties')  x1
                            , XMLTABLE (XMLNAMESPACES(DEFAULT 'http://com.oocl.schema.tnm.agreementbuilder'),
                                            '/ShippingParties'
                                            PASSING x1.SP
                                            COLUMNS
                                               SAP_ID VARCHAR2(10) PATH 'CustomerHolder/SAP_ID',
                                               NC XMLType PATH 'NamedCustomerGroups/NamedCustomer' ) x2
                             , XMLTABLE (XMLNAMESPACES(DEFAULT 'http://com.oocl.schema.tnm.agreementbuilder'),
                                               '/NamedCustomer' 
                                               PASSING x2.NC
                                               COLUMNS
                                                  SW XMLType PATH 'SAP_IDs/StringWrappers') x3
                             , XMLTABLE (XMLNAMESPACES(DEFAULT 'http://com.oocl.schema.tnm.agreementbuilder'),
                                               '/StringWrappers' 
                                               PASSING x3.SW
                                               COLUMNS
                                                  VALUE VARCHAR2(15) PATH 'Value'
                                               ) x4
                            ;
                            *OFFICIALLY*, if I am not mistaken, then you are allowed to *ONLY* use the *VIRTUAL* column *ONCE*.

                            You used it 4 times in your create XMLINDEX statement. I know you can use it mutlple times, because I tested it once and pointed it out to the development team. Probably the syntax can be extended as long as the max string length for PARAMETERS. That said, I don't know what happens in the Oracle kernel / ODCI part and if the development team has taken something like this into account.

                            DESPITE that, I don't think it is allowed. Didn't really checkup on it in the docs, but it wasn't when I was testing this during the beta trails for Oracle 11gR2.

                            As you might notice Odie/Marc's example ALSO included only ONE virtual column passing on the XMLTYPE frament result...

                            To be on the safe side create multiple content tables / xmlindex constructs by extending the GROUP or create multiple GROUPS...

                            See for more info here: http://www.liberidu.com/blog/?p=1805


                            HTH

                            M

                            Edited by: Marco Gralike on Mar 23, 2011 12:09 AM
                            • 11. Re: XMLIndex is not getting used
                              Marco Gralike
                              In the 11.2 XMLDB document the following can be found in the XMLINDEX chapter

                              http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb_indexing.htm#BCGCBDHH
                              The keyword VIRTUAL is required for an XMLType column. It specifies that the XMLType column itself is not materialized: 
                              its data is stored in the XMLIndex index only in the form of the relational columns specified by its corresponding XMLTable table.
                              
                              You cannot create more than one XMLType column in a given XMLTable clause. To achieve that effect, you must instead define an additional group.
                              "You cannot create more than *one* XMLType column in a given XMLTable clause. To achieve that effect, you must *instead define an additional group*."

                              Edited by: Marco Gralike on Mar 23, 2011 12:07 AM
                              • 12. Re: XMLIndex is not getting used
                                644524
                                Hi Marco,

                                Really appreciate your outstanding help and support in this Issue.

                                I am just reviewing your 2 latest posts (highlighting the use of VIRTUAL only once)
                                and will try and explore the creation of Additional Groups feature.

                                FYI, below is Appln query in its original form which is doing a SELECT
                                based on XML Element value occurring in any one of two Nested Collections

                                SELECT *
                                FROM tnmab_agreement_xml A
                                WHERE
                                -- SAP_ID CHECKING - AGREEMENT SHIPPING PARTY AND NAMED CUSTOMER MUST OVERLAP
                                ( existsNode(A.AGREEMENT_XML,'/Agreement/ShippingPartyGroups/ShippingParties/CustomerHolder/SAP_ID[''5000003001'']') = 1
                                OR existsNode(A.AGREEMENT_XML,'/Agreement/NamedCustomerGroups/NamedCustomer/SAP_IDs/StringWrappers/Value[''5000003001'']') = 1
                                )

                                This is the one currently struggling to convert to XQuery SQL written on Binary XML Table

                                Original DDL for O-R XML Table used 2 Nested Table Indexes to help in above search which
                                I am sharing as additional background to this problem:

                                -CREATE TABLE TNMAB_AGREEMENT_XML
                                (
                                     AGREEMENT_XML           xmltype          NOT NULL,
                                     CREATE_BY          VARCHAR2(15)      NULL ,
                                     CREATE_DT_GMT          TIMESTAMP      NULL ,
                                     CREATE_CLIENT_ID      VARCHAR2(65)      NULL ,
                                     UPDATE_BY          VARCHAR2(15)      NULL ,
                                     UPDATE_DT_GMT          TIMESTAMP      NULL ,
                                     UPDATE_CLIENT_ID      VARCHAR2(65)      NULL ,
                                     PIC_VERSION_NUM          NUMBER(20)      NULL
                                )
                                     XMLTYPE COLUMN AGREEMENT_XML XMLSCHEMA "AB_Agreement_V1_XMLDB.xsd" ELEMENT "Agreement"
                                     VARRAY AGREEMENT_XML.XMLDATA."SHIPPING_PARTY_GROUPS"
                                          STORE AS TABLE SHIPPING_PARTY_GROUPS_NT
                                     (
                                          (PRIMARY KEY (NESTED_TABLE_ID, ARRAY_INDEX) ENABLE)
                                          VARRAY SHIPPING_PARTIES
                                               STORE AS TABLE SHIPPING_PARTIES_NT
                                          (
                                               (PRIMARY KEY (NESTED_TABLE_ID, ARRAY_INDEX) ENABLE)
                                          )
                                     )
                                     VARRAY AGREEMENT_XML.XMLDATA."SALES_OFFICE_CODES"."STRING_WRAPPERS"
                                          STORE AS TABLE SALES_OFFICE_CODES_NT
                                          (
                                               (PRIMARY KEY (NESTED_TABLE_ID, ARRAY_INDEX) ENABLE)
                                          )
                                TABLESPACE TNMAB_XMLDB_ME_DATA;


                                --Nested Table Indexes
                                create index TNMAB_AGREEMENT_XML_NT_IDX1 on SALES_OFFICE_CODES_NT NT(NT.STR_VAL, NT.NESTED_TABLE_ID)
                                tablespace TNMAB_XMLDB_ME_INDX;

                                create index TNMAB_AGREEMENT_XML_NT_IDX2 on SHIPPING_PARTIES_NT NT(NT.CUSTOMER_HOLDER.SAP_ID, NT.NESTED_TABLE_ID)
                                tablespace TNMAB_XMLDB_ME_INDX;


                                Essentially above is the DDL we are now trying to convert to Binary XML table making use of XMLIndex

                                In case it helps understand better our background

                                FYI, above query is doing good in Production System


                                Many Thanks again for all your help and support

                                Regards
                                Auro
                                • 13. Re: XMLIndex is not getting used
                                  Marco Gralike
                                  Do you currently have a primary key for table TNMAB_AGREEMENT_XML ?
                                  • 14. Re: XMLIndex is not getting used
                                    Marco Gralike
                                    Also what I find a bit strange is that you use

                                    http://com.oocl.schema.tnm.agreementbuilder

                                    in your xmlindex statement

                                    AB_Agreement_V1_XMLDB.xsd

                                    in your XMLTYPE OR column create table definition

                                    and wrestling with a query where existnode doesn't make use of whatsoever namespace reference...
                                    SELECT * 
                                    FROM tnmab_agreement_xml A
                                    WHERE 
                                    -- SAP_ID CHECKING - AGREEMENT SHIPPING PARTY AND NAMED CUSTOMER MUST OVERLAP
                                    ( existsNode(A.AGREEMENT_XML,'/Agreement/ShippingPartyGroups/ShippingParties/CustomerHolder/SAP_ID[''5000003001'']') = 1
                                    OR existsNode(A.AGREEMENT_XML,'/Agreement/NamedCustomerGroups/NamedCustomer/SAP_IDs/StringWrappers/Value[''5000003001'']') = 1
                                    )
                                    regarding this query, you should replace existnode with the XMLEXIST syntax because this is the preference plus its better supported with xmlindex and binary xml.
                                    1 2 Previous Next