This discussion is archived
1 2 Previous Next 16 Replies Latest reply: May 7, 2013 9:49 AM by KnightOfBlueArmor RSS

Structured XMLIndex is not being used

KnightOfBlueArmor Newbie
Currently Being Moderated
I have a table defined as "TABLE OF XMLTYPE" with XML Binary storage with a structured XMLIndex under Oracle 11.2.0.3.4. The query that I am using on this table is virtually the same as the XMLIndex, but it's not being used. I searched the forums for similar issues and found this:

XMLIndex is not getting used

However, the post is a couple of years old, and I think that the solution was really specific to the problem. Not that mine isn't. ;)
Per the forum guidelines, the data/DDL is confidential, and should not be posted, so I opened a SR for it - SR 3-7160281751.

May I please have some help understanding why the structured XMLIndex is not being used?

Thanks...
  • 1. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    KnightOfBlueArmor wrote:
    Per the forum guidelines, the data/DDL is confidential, and should not be posted, so I opened a SR for it - SR 3-7160281751.
    What's the point of opening this thread then?

    The majority of us here cannot see the content of the SR (except for Mark D. probably) so what kind of help do you want?

    Could you at least try to reproduce on a similar structure with dummy data so that we can work on something?

    The query that I am using on this table is virtually the same as the XMLIndex, but it's not being used.
    Sometimes you think it's the same but may differ due datatype mismatches for example.
    Sometimes, it's just a stupid typo that doesn't produce any error but ultimately prevents the CBO from recognizing the pattern and using the index.

    Review the tips given in this document and try to see if you're doing something wrong :
    http://www.oracle.com/technetwork/database-features/xmldb/xmlqueryoptimize11gr2-168036.pdf

    Edited by: odie_63 on 4 mai 2013 18:24
  • 2. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    odie_63 wrote:
    What's the point of opening this thread then?

    The majority of us here cannot see the content of the SR (except for Mark D. probably) so what kind of help do you want?

    Could you at least try to reproduce on a similar structure with dummy data so that we can work on something?
    I simply followed the guidelines listed in the thread "Please read carefully before starting a new thread." which says,
    7. If you need to upload large or confidential files please open a tar with support, upload the files under the tar number and post the tar number here. If the uploaded files contain confidential information please ensure that your post clearly indiciates I should not post any of the contents of the uploaded files in the forum when replying to your question.
    The data in question contains 130,000 XML records of varying sizes of between 300K and 3MB, following a 50 MB schema spread across 60 XSD files, one of which changes twice daily. It is very difficult to reproduce or mock up said data in this case, but I will try.

    Thank you for the link, I will review it.
  • 3. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    KnightOfBlueArmor wrote:
    I simply followed the guidelines listed in the thread "Please read carefully before starting a new thread."
    OK.
    I just wanted to pinpoint that you probably won't get much help on the forum from regular "non-corporate" users, other than general advices.

    Hope you get answers from the Support.
  • 4. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    OK, I did the best I could to clean this up. The last query in this script is the one not using the index. The tables "book_master" and "book_join_temp" are populated, though I didn't include the data here. Do you see anything wrong?
    --Create tables...
    
    CREATE TABLE book_master OF XMLTYPE
    XMLTYPE STORE AS SECUREFILE BINARY XML 
    VIRTUAL COLUMNS
    (
      isbn_nbr AS ( XmlCast(
                    XmlQuery('declare namespace plh="http://www.mrbook.com/InventoryData/Listing";
                              declare namespace invtdata="http://www.mrbook.com/Inventory";
                              /invtdata:INVENTORY/plh:LIST/plh:ISBN_NBR' 
                              PASSING object_value RETURNING CONTENT) AS VARCHAR2(64)) ),
      book_id AS ( XmlCast(
                    XmlQuery('declare namespace plh="http://www.mrbook.com/InventoryData/Listing";
                              declare namespace invtdata="http://www.mrbook.com/Inventory";
                              /invtdata:INVENTORY/plh:LIST/plh:BOOK_ID' 
                              PASSING object_value RETURNING CONTENT) AS VARCHAR2(64)) )
    );
    
    CREATE GLOBAL TEMPORARY TABLE book_join_temp
    (
       isbn_nbr VARCHAR2(64),
       book_id VARCHAR2(64),
       row_num INT,
       PRIMARY KEY(row_num)
    ) ON COMMIT DELETE ROWS;
    
    --Create indices....
    
    CREATE INDEX bkm_xmlindex_ix ON book_master (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATH TABLE path_tab');
    
    BEGIN
       DBMS_XMLINDEX.registerParameter(
          'myparam',
          'ADD_GROUP GROUP book_record
             XMLTable bk_idx_tab 
             XmlNamespaces(''http://www.mrbook.com/InventoryData/Listing'' AS "plh",
                      ''http://www.mrbook.com/Inventory'' AS "invtdata", 
                      ''http://www.mrbook.com/BookInfo'' AS "idty",
                      ''http://www.mrbook.com/References'' AS "lclone",
                      ''http://www.mrbook.com/Publishing'' AS "trd",
                      ''http://www.mrbook.com'' AS "mrbook"),
             ''/invtdata:INVT_DATA''
               COLUMNS
                    xml_id    RAW(16)     PATH ''/@XML_ID'',
                    isbn_nbr  VARCHAR(64) PATH ''/plh:LIST/plh:ISBN_NBR'',
                    book_id   VARCHAR(64) PATH ''/plh:LIST/plh:BOOK_ID'',
                    
                    seller_loc_id NUMBER(13,0) PATH ''/plh:LIST/plh:SELLER_LOC_ID'',
                    
                    catg_typ_cd NUMBER(7,0) PATH ''/idty:BK_INFO/idty:CATG_TYP_CD'',
                    CTRY_MKT_LOC NUMBER(7,0) PATH ''/idty:BK_INFO/idty:CTRY_MKT_LOC'',
                    bk_out_of_print_cd NUMBER(7,0) PATH ''/idty:BK_INFO/idty:BK_OUT_OF_PRINT_CD'',
                    
                    reprint_isbn_nbr VARCHAR2(64) PATH ''/idty:BK_INFO/idty:REPRINT_ISBN_NBR'',
                    reprint_book_id VARCHAR2(64) PATH ''/idty:BK_INFO/idty:REPRINT_BOOK_ID'',
                    
                    orig_ed_isbn_nbr VARCHAR2(64) PATH ''/lclone:REFERENCES/lclone:PRINT[child::lclone:PRINT_TYP_CD="160"]/lclone:ISBN_NBR'',
                    orig_ed_book_id VARCHAR2(64) PATH ''//lclone:REFERENCES/lclone:PRINT[child::lclone:PRINT_TYP_CD="160"]/lclone:BOOK_ID'',
                    
                    last_mod_dt TIMESTAMP PATH ''/node()[local-name()="LAST_MOD_DT"]'',
                    
                    subject_catg_code  NUMBER(7) PATH ''/idty:BK_INFO/idty:SUBJECT_CATG_CODE[child::idty:CATG_REF_LVL=1]/idty:SUBJECT_CATG_CODE'',
                    catg_code VARCHAR2(48) PATH ''/idty:BK_INFO/idty:SUBJECT_CATG_CODE[child::idty:CATG_REF_LVL=1]/idty:CATG_CODE'',
                    
                    pub_summ  XMLType   PATH ''/trd:PUB_SUMM'' VIRTUAL
                    
                XMLTable trd_summ_entr_ix 
                XmlNamespaces(''http://www.mrbook.com/InventoryData/Listing'' AS "plh",
                      ''http://www.mrbook.com/Inventory'' AS "invtdata", 
                      ''http://www.mrbook.com/BookInfo'' AS "idty",
                      ''http://www.mrbook.com/References'' AS "lclone",
                      ''http://www.mrbook.com/Publishing'' AS "trd",
                      ''http://www.mrbook.com'' AS "mrbook"),
                   ''/trd:PUB_SUMM/trd:PUBLC'' PASSING pub_summ
                COLUMNS
                    pub_yrmo  VARCHAR2(6) PATH ''/@PUBLC_YRMO''
            ');
    END;
    
    ALTER INDEX bk_xmlindex_ix PARAMETERS('PARAM myparam');
    
    CREATE INDEX ejt_isbn ON book_join_temp(isbn_nbr);
    CREATE INDEX ejt_book ON book_join_temp(book_id);
    
    --Using the PATH table instead of structured index???
    
    SELECT
        ej.row_num,
        e.xml_id,
        
        e.isbn_nbr,
        e.book_id,
        
        e.seller_loc_id,
        e.seller_loc_id AS mkt_seller_id,
        
        e.catg_typ_cd,
        e.CTRY_MKT_LOC,
        e.bk_out_of_print_cd,
        
        e.reprint_isbn_nbr,
        e.reprint_book_id,
        
        e.orig_ed_isbn_nbr,
        e.orig_ed_book_id,
        
        g.OBJECT_VALUE AS invt_data
        
    FROM 
        book_master g,
        book_join_temp ej,
        
        XmlTable(
        XmlNamespaces('http://www.mrbook.com/InventoryData/Listing' AS "plh",
                      'http://www.mrbook.com/Inventory' AS "invtdata", 
                      'http://www.mrbook.com/BookInfo' AS "idty",
                      'http://www.mrbook.com/References' AS "lclone",
                      'http://www.mrbook.com' AS "mrbook"),
          '/invtdata:INVENTORY'
        
        PASSING g.OBJECT_VALUE
        COLUMNS
           xml_id PATH '@XML_ID',
           isbn_nbr VARCHAR2(64) PATH 'plh:LIST/plh:ISBN_NBR',
           book_id VARCHAR2(64) PATH 'plh:LIST/plh:BOOK_ID',
           
           seller_loc_id NUMBER PATH 'plh:LIST/plh:SELLER_LOC_ID',
           
           catg_typ_cd NUMBER PATH 'idty:BK_INFO/idty:CATG_TYP_CD',
           CTRY_MKT_LOC NUMBER PATH 'idty:BK_INFO/idty:CTRY_MKT_LOC',
           bk_out_of_print_cd NUMBER PATH 'idty:BK_INFO/idty:BK_OUT_OF_PRINT_CD',
           
           reprint_isbn_nbr NUMBER PATH 'idty:SUBJ_DTL/idty:SUCSR_DUNS_NBR',
           reprint_book_id NUMBER PATH 'idty:SUBJ_DTL/idty:SUCSR_SUBJ_ID',
           
           orig_ed_isbn_nbr VARCHAR2(64) PATH '/lclone:REFERENCES/lclone:PRINT[child::lclone:PRINT_TYP_CD="160"]/lclone:ISBN_NBR',
           orig_ed_book_id VARCHAR2(64) PATH '/lclone:REFERENCES/lclone:PRINT[child::lclone:PRINT_TYP_CD="160"]/lclone:BOOK_ID'
    ) e
    WHERE
         ej.isbn_nbr = e.isbn_nbr
    OR  ej.book_id = e.book_id;
  • 5. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    Do you see anything wrong?
    Yes, the PATH expressions are all wrong.
    You must not begin the path with a forward slash unless the first step is the context item itself.

    This point was already raised in one of your previous thread : {message:id=10930951}
    >
    You'll probably hit some more errors at runtime though, some of them due to inconsistencies in the PATH expressions, e.g.
    idty:BOOK_INFO/idty:lang_code
    //idty:BOOK_INFO/idty:BOOK_ENTR[child::idty:BOOK_REF=1]/idty:CATG_CODE
    /idty:BOOK_INFO/idty:OUT_OF_STOCK_REAS
    You almost got it right in the query though, except for :
    orig_ed_isbn_nbr VARCHAR2(64) PATH '/lclone:REFERENCES/lclone:PRINT[child::lclone:PRINT_TYP_CD="160"]/lclone:ISBN_NBR',
    orig_ed_book_id VARCHAR2(64) PATH '/lclone:REFERENCES/lclone:PRINT[child::lclone:PRINT_TYP_CD="160"]/lclone:BOOK_ID'
  • 6. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    I see your point; all of the tags in question are under invtdata:INVENTORY, so I believe that is the context node in question. However, I did receive runtime errors when I made the query match the index, so I removed all of the leading slashes from both the query and the index definition. Unfortunately, the explain plan still uses only PATH_TAB. I've seen several threads mentioning that the XMLTable part of a query may not be exactly the same as the structured index definition. Is that also the case here?

    Thanks.
  • 7. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    KnightOfBlueArmor wrote:
    However, I did receive runtime errors when I made the query match the index, so I removed all of the leading slashes from both the query and the index definition.
    The usual way to do things is to make the index match the query, not the other way around. That way you're sure you have an index that could "theoretically" be used to resolve the query.
    Unfortunately, the explain plan still uses only PATH_TAB.
    Maybe it's just that the CBO finds that the unstructured part is better to use in this case.

    What if you remove the unstructured part?

    Are all stats up-to-date?
  • 8. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    I thought all of the parts of the query were structured; is there an unstructured part that I'm just not seeing?

    I have run EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(). Is this sufficient, or do I need to collect statistics on the individual tables and indices?
  • 9. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    KnightOfBlueArmor wrote:
    I thought all of the parts of the query were structured; is there an unstructured part that I'm just not seeing?
    I mean the unstructured component of the index .
    I have run EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS().
    It should be OK.
  • 10. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    By unstructured, do you mean this part of the index?
    pub_summ  XMLType   PATH ''trd:PUB_SUMM'' VIRTUAL
    ...or the unstructured part when I initially created the index?
    CREATE INDEX bkm_xmlindex_ix ON book_master (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PATH TABLE path_tab');
    How do I drop an unstructured part with PATH_TAB after it's already been created?
    I had tried to create the index with PARAMETERS ('PARAM myparam') before, but I received errors...
  • 11. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    ...or the unstructured part when I initially created the index?
    Yes.
    How do I drop an unstructured part with PATH_TAB after it's already been created?
    It is explained in the documentation :
    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_indexing.htm#autoId39
    alter index bkm_xmlindex_ix parameters('drop path table');
    I had tried to create the index with PARAMETERS ('PARAM myparam') before, but I received errors...
    Which ones?
  • 12. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    If I try to create the index initially with no unstructured component, like this...
    CREATE INDEX book_xmlindex_ix ON book_master (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PARAM myparam');
    I see this error:
    Error starting at line 27 in command:
    CREATE INDEX book_xmlindex_ix ON book_master (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PARAM myparam')
    Error at Command Line:27 Column:0
    Error report:
    SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
    ORA-30969: invalid syntax for PARAMETERS
    29958. 00000 -  "fatal error occurred in the execution of ODCIINDEXCREATE routine"
    *Cause:    Failed to successfully execute the ODCIIndexCreate routine.
    *Action:   Check to see if the routine has been coded correctly.
    I'll try dropping the PATH_TAB and see if that helps.
  • 13. Re: Structured XMLIndex is not being used
    KnightOfBlueArmor Newbie
    Currently Being Moderated
    OK, so after dropping the path table as you recommended, the explain plan now says the query will hit the index. Yay!

    The bad news is that the performance is terrible.

    Also, I'm seeing a lot of this in the database logs. This seems to happen any time I touch that structured index; whether I alter the index to use the parameter, run a query that hits it, rebuild statistics, and so on:
    Errors in file /u01/app/oracle/diag/rdbms/bkdev/bkdev/trace/bkdev_ora_30900.trc  (incident=249418):
    ORA-00600: internal error code, arguments: [qmxtrtBuildFSTFromGrpDesc1], [0], [], [], [], [], [], [], [], [], [], []
    Use ADRCI or Support Workbench to package the incident.
    See Note 411.1 at My Oracle Support for error and packaging details.
  • 14. Re: Structured XMLIndex is not being used
    odie_63 Guru
    Currently Being Moderated
    KnightOfBlueArmor wrote:
    I see this error:
    SQL Error: ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
    ORA-30969: invalid syntax for PARAMETERS
    That's because, "ADD_GROUP" directive only works with ALTER INDEX.
    If you're creating the index with a structured component directly, first register the parameter with the GROUP directive only :
    BEGIN
       DBMS_XMLINDEX.registerParameter(
          'myparam',
          'GROUP book_record
             XMLTable bk_idx_tab 
         ...
    then this will work :
    CREATE INDEX bkm_xmlindex_ix ON book_master (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PARAM myparam');
1 2 Previous Next