This discussion is archived
1 2 Previous Next 29 Replies Latest reply: Mar 31, 2011 3:27 PM by MarcoGralike RSS

XMLIndex is not getting used

644524 Newbie
Currently Being Moderated
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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Did the XMLTABLE syntax you based your XMLINDEX upon return any rows...?
  • 2. Re: XMLIndex is not getting used
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Did you create any statistics on the tables involved...?
  • 3. Re: XMLIndex is not getting used
    644524 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    You never answered the following...
    Did the XMLTABLE syntax you based your XMLINDEX upon return any rows...?
  • 8. Re: XMLIndex is not getting used
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Do you currently have a primary key for table TNMAB_AGREEMENT_XML ?
  • 14. Re: XMLIndex is not getting used
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points