1 2 Previous Next 16 Replies Latest reply: May 7, 2013 11:49 AM by KnightOfBlueArmor RSS

    Structured XMLIndex is not being used

    KnightOfBlueArmor
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              ...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
                                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
                                  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
                                    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