1 2 Previous Next 16 Replies Latest reply: Apr 24, 2012 2:05 PM by 925561 RSS

    Why Isn't xmlindex being used in slow query on binary xml table eval?

    925561
      I am running a slow simple query on Oracle database server 11.2.0.1 that is not using an xmlindex. Instead, a full table scan against the eval binary xml table occurs. Here is the query:
       select -- /*+ NO_XMLINDEX_REWRITE no_parallel(eval)*/
            defid from eval,
            XMLTable(XMLNAMESPACES(DEFAULT 'http://www.cigna.com/acme/domains/eval/2010/03',
            'http://www.cigna.com/acme/domains/derived/fact/2010/03' AS "ns7"),
            '$doc/eval/derivedFacts/ns7:derivedFact' passing eval.object_value as "doc" columns defid varchar2(100) path 'ns7:defId'
             ) eval_xml
      where eval_xml.defid in ('59543','55208'); 
      The predicate is not selective at all - the returned row count is the same as the table row count (325,550 xml documents in the eval table). When different values are used bringing the row count down to ~ 33%, the xmlindex still isn't used - as would be expected in a purely relational nonXML environment.

      My question is why would'nt the xmlindex be used in a fast full scan manner versus a full table scan traversing the xml in each eval table document record?
      Would a FFS hint be applicable to an xmlindex domain-type index?

      Here is the xmlindex definition:
       CREATE INDEX "EVAL_XMLINDEX_IX" ON "EVAL" (OBJECT_VALUE)
        INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS
        ('XMLTable eval_idx_tab XMLNamespaces(DEFAULT ''http://www.cigna.com/acme/domains/eval/2010/03'',
        ''http://www.cigna.com/acme/domains/derived/fact/2010/03'' AS "ns7"),''/eval'' 
             COLUMNS defId VARCHAR2(100) path ''/derivedFacts/ns7:derivedFact/ns7:defId''');
      Here is the eval table definition:
       CREATE
        TABLE "N98991"."EVAL" OF XMLTYPE
        (
          CONSTRAINT "EVAL_ID_PK" PRIMARY KEY ("EVAL_ID") USING INDEX PCTFREE 10
          INITRANS 4 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 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
        (
          "EVAL_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/eval/2010/03"; (::)
      /eval/@eval_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))),
          "EVAL_CAT" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/eval/2010/03";/eval/@category'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(50))),
          "ACME_MBR_ID" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/eval/2010/03";/eval/@acmeMemberId'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(50))),
          "EVAL_ID" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/eval/2010/03";/eval/@evalId'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(50)))
        )
        PCTFREE 0 PCTUSED 80 INITRANS 4 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" ; 
      Sample cleansed xml snippet:
      <?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?><eval createdById="xxxx" hhhhMemberId="37e6f05a-88dc-41e9-a8df-2a2ac6d822c9" category="eeeeeeee" eval_dt="2012-02-11T23:47:02.645Z" evalId="12e007f5-b7c3-4da2-b8b8-4bf066675d1a" xmlns="http://www.xxxxx.com/vvvv/domains/eval/2010/03" xmlns:ns2="http://www.cigna.com/nnnn/domains/derived/fact/2010/03" xmlns:ns3="http://www.xxxxx.com/vvvv/domains/common/2010/03">
         <derivedFacts>
            <ns2:derivedFact>
               <ns2:defId>12345</ns2:defId>
               <ns2:defUrn>urn:mmmmrunner:Medical:Definition:DerivedFact:52657:1</ns2:defUrn>
               <ns2:factSource>tttt Member</ns2:factSource>
               <ns2:origInferred_dt>2012-02-11T23:47:02.645Z</ns2:origInferred_dt>
               <ns2:factValue>
                  <ns2:type>boolean</ns2:type>
                  <ns2:value>true</ns2:value>
               </ns2:factValue>
            </ns2:derivedFact>
            <ns2:derivedFact>
               <ns2:defId>52600</ns2:defId>
               <ns2:defUrn>urn:ddddrunner:Medical:Definition:DerivedFact:52600:2</ns2:defUrn>
               <ns2:factSource>cccc Member</ns2:factSource>
               <ns2:origInferred_dt>2012-02-11T23:47:02.645Z</ns2:origInferred_dt>
               <ns2:factValue>
                  <ns2:type>string</ns2:type>
                  <ns2:value>null</ns2:value>
               </ns2:factValue>
            </ns2:derivedFact>
            <ns2:derivedFact>
               <ns2:defId>59543</ns2:defId>
               <ns2:defUrn>urn:ddddunner:Medical:Definition:DerivedFact:52599:1</ns2:defUrn>
               <ns2:factSource>dddd Member</ns2:factSource>
               <ns2:origInferred_dt>2012-02-11T23:47:02.645Z</ns2:origInferred_dt>
               <ns2:factValue>
                  <ns2:type>string</ns2:type>
                  <ns2:value>INT</ns2:value>
               </ns2:factValue>
            </ns2:derivedFact>
                      ...
                  With the repeating <ns2:derivedFact> element continuing under the <derivedFacts>
      The Oracle XML DB Developer's Guide 11g Release 2 isn't helping much...

      Any assitance much appreciated.

      Regards,
      Rick Blanchard
        • 1. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
          Marco Gralike
          I am guessing I am missing something... Aren't you not explicitly enforcing no xmlindex to be used via the hint (aka so thats what you get - a full table scan)?
           /*+ NO_XMLINDEX_REWRITE no_parallel(eval)*/
          • 2. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
            Marco Gralike
            I don't think a FFS hint will work on a domain index, it might be on a secondary index ON the domain index (structured XMLIndex). Not sure but guessing (sounds plausible to me) that a FFS scan can not be done on a domain index (different scenario's needed for ODCI based indexes such as spatial, text or xmltype indexes). A xmltype index can be seen as a protected table with multiple columns (so no leaf block or clustering factor in the sense of a B-Tree index available)

            http://docs.oracle.com/cd/B10500_01/server.920/a96533/optimops.htm

            The access path determines the number of units of work required to get data from a base table. The access path can be a table scan, a fast full index scan, or an index scan. During table scan or fast full index scan, multiple blocks are read from the disk in a single I/O operation. Therefore, the cost of a table scan or a fast full index scan depends on the number of blocks to be scanned and the multiblock read count value. The cost of an index scan depends on the levels in the B-tree, the number of index leaf blocks to be scanned, and the number of rows to be fetched using the rowid in the index keys. The cost of fetching rows using rowids depends on the index clustering factor.

            Edited by: Marco Gralike on Apr 11, 2012 11:25 PM
            • 3. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
              odie_63
              Hi,

              Are you the same Rick Blanchard who posted here recently? :)

              If so, I think we've already been through this. You must match query and index structure.

              Whereas the query is correct, the way you defined the index doesn't make much sense in my opinion because you're trying to extract a single value out of a sequence.
              I'm very surprised you didn't even get this at creation time :
              ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
              Based on your sample document (thanks for giving it), the correct approach would be :
              SQL> CREATE INDEX "EVAL_XMLINDEX_IX" ON "EVAL" (OBJECT_VALUE)
                2    INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS
                3    ('XMLTable eval_idx_tab XMLNamespaces(DEFAULT ''http://www.xxxxx.com/vvvv/domains/eval/2010/03'',
                4    ''http://www.cigna.com/nnnn/domains/derived/fact/2010/03'' AS "ns2"),
                5    ''/eval/derivedFacts/ns2:derivedFact''
                6         COLUMNS defId VARCHAR2(100) path ''ns2:defId''');
              
              Index created.
              
              SQL> exec dbms_stats.gather_table_stats(user, 'EVAL');
              
              PL/SQL procedure successfully completed.
              
              SQL> set lines 120
              SQL> set autotrace traceonly
              SQL> select defid from eval,
                2        XMLTable(XMLNAMESPACES(DEFAULT 'http://www.xxxxx.com/vvvv/domains/eval/2010/03',
                3        'http://www.cigna.com/nnnn/domains/derived/fact/2010/03' AS "ns2"),
                4        '$doc/eval/derivedFacts/ns2:derivedFact'
                5        passing eval.object_value as "doc"
                6        columns defid varchar2(100) path 'ns2:defId'
                7         ) eval_xml
                8  ;
              
              300000 rows selected.
              
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 2206239415
              
              -----------------------------------------------------------------------------------
              | Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT   |              |   327K|    12M|   475   (7)| 00:00:06 |
              |   1 |  NESTED LOOPS      |              |   327K|    12M|   475   (7)| 00:00:06 |
              |   2 |   TABLE ACCESS FULL| EVAL_IDX_TAB |   327K|  7352K|   446   (1)| 00:00:06 |
              |*  3 |   INDEX UNIQUE SCAN| SYS_C007053  |     1 |    17 |     0   (0)| 00:00:01 |
              -----------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 3 - access("EVAL"."SYS_NC_OID$"="SYS_SXI_0"."OID")
              
              
              Statistics
              ----------------------------------------------------------
                       91  recursive calls
                        0  db block gets
                   343118  consistent gets
                      986  physical reads
                    74492  redo size
                  4700375  bytes sent via SQL*Net to client
                   220409  bytes received via SQL*Net from client
                    20001  SQL*Net roundtrips to/from client
                        4  sorts (memory)
                        0  sorts (disk)
                   300000  rows processed
              • 4. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                925561
                Marco,

                I've got the hint commented out with the 2 dashes in front. I use this hint, at times,
                to compare behavior with and without rewrite.
                • 5. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                  925561
                  odie 63

                  Yes this is me Rick Blanchard :D. I have 2 accounts - one on site here at cigna and my own SRS Engineering, Inc. account.

                  Guess I had to live thru this example to 'puctuate' the importance of matching the query structure with the index structure. When I synced up the XQuery string in the xmlindex table clause and the path string in the column clause, so the XMLIndex agreed with the query; the explain plan showed the xmlindex being used via a fast full scan occurring and the eval_idx_tab content table of the xmlindex being used. Thank-you for expounding on this point wiith your illustrations. :)

                  This sql was simplified in my desperate attempt to get the xmlindex being used...
                   select defid from eval,
                           XMLTable(XMLNAMESPACES(DEFAULT 'http://www.xxxxx.com/vvvv/domains/eval/2010/03',
                          'http://www.cigna.com/nnnn/domains/derived/fact/2010/03' AS "ns2"),
                           '$doc/eval/derivedFacts/ns2:derivedFact'
                          passing eval.object_value as "doc"
                          columns defid varchar2(100) path 'ns2:defId'
                          ) eval_xml 
                  The actual sql presented by the develo[per as extremely slow is this:

                  {code} SELECT -- /*+ NO_XMLINDEX_REWRITE no_parallel(eval)*/
                  defid, eval_catt from eval,
                  XMLTable( XMLNAMESPACES( DEFAULT 'http://www.cigna.com/acme/domains/eval/2010/03', 'http://www.cigna.com/acme/domains/derived/fact/2010/03' AS "ns7" ),
                  '/eval' passing eval.object_value
                  COLUMNS defid VARCHAR2(100) path 'derivedFacts/ns7:derivedFact/ns7:defId',
                  eval_catt varchar2(50) path '@category'
                  ) eval_xml
                  where eval_xml.defid in ('59543','55208') and eval_xml.eval_catt like 'internal';

                  I suspect this query is also not selective, but would benefit from xmlindex usage - ina simlar vein as for the simple query.

                  The corresponding xmlindex - in its present form is:

                  CREATE INDEX "EVAL_XMLINDEX_IX" ON "EVAL" (OBJECT_VALUE)
                  INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS
                  ('XMLTable eval_idx_tab XMLNamespaces (''http://www.cigna.com/acme/domains/derived/fact/2010/03'' AS "ns7",
                  DEFAULT ''http://www.cigna.com/acme/domains/eval/2010/03''),''/eval''
                  COLUMNS defId VARCHAR2(100) path ''derivedFacts/ns7:derivedFact/ns7:defId'',
                  eval_catt VARCHAR2(50) path ''@category''');


                  This index creation produced the 'ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence...' error.
                  Also the above sql query didn't produce any rows...

                  The eval_catt is an attribute at the root node 'eval' element level spanning many derivedFact descendent elements aech containing a defid element.

                  Will try your approach from the previous thread for 1 -> many relationships; and review the multilevel chaining discussion on pg 6-29 in the Oracle XML DB Developer's Guide 11g Release 2 (11.2) E23094-01.

                  Regards,
                  Rick Blanchard
                  • 6. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                    925561
                    odie 63, et. al.;

                    Attached is the reworked select query, xmlindex, and 2ndary indexes. Note: though namespaces are used; we're not registering any schema defns.
                    SELECT /*+ NO_USE_HASH(eval) +/ --/*+ NO_QUERY_REWRITE no_parallel(eval)*/
                    eval_xml.eval_catt, df.defid FROM eval,
                    --df.defid FROM eval,
                    XMLTable(XMLNamespaces( DEFAULT 'http://www.cigna.com/acme/domains/eval/2010/03',
                                            'http://www.cigna.com/acme/domains/derived/fact/2010/03' AS "ns7" ),
                            '/eval' passing eval.object_value 
                             COLUMNS
                               eval_catt VARCHAR2(50) path '@category',
                               derivedFact XMLTYPE path '/derivedFacts/ns7:derivedFact')eval_xml,
                    XMLTable(XMLNamespaces('http://www.cigna.com/acme/domains/derived/fact/2010/03' AS "ns7",
                                              DEFAULT 'http://www.cigna.com/acme/domains/eval/2010/03'),
                            '/ns7:derivedFact' passing eval_xml.derivedFact
                             COLUMNS 
                               defid VARCHAR2(100) path 'ns7:defId') df
                    WHERE df.defid IN ('52657','52599') AND eval_xml.eval_catt LIKE 'external';
                    --where df.defid = '52657';
                    SELECT /*+ NO_USE_HASH(eval +/ --/*+ NO_QUERY_REWRITE no_parallel(eval)*/
                    eval_xml.eval_catt, df.defid FROM eval,
                    --df.defid FROM eval,
                    XMLTable(XMLNamespaces( DEFAULT 'http://www.cigna.com/acme/domains/eval/2010/03',
                                            'http://www.cigna.com/acme/domains/derived/fact/2010/03' AS "ns7" ),
                            '/eval' passing eval.object_value 
                             COLUMNS
                               eval_catt VARCHAR2(50) path '@category',
                               derivedFact XMLTYPE path '/derivedFacts/ns7:derivedFact')eval_xml,
                    XMLTable(XMLNamespaces('http://www.cigna.com/acme/domains/derived/fact/2010/03' AS "ns7",
                                              DEFAULT 'http://www.cigna.com/acme/domains/eval/2010/03'),
                            '/ns7:derivedFact' passing eval_xml.derivedFact
                             COLUMNS 
                               defid VARCHAR2(100) path 'ns7:defId') df
                    WHERE df.defid IN ('52657','52599') AND eval_xml.eval_catt LIKE 'external';
                    --where df.defid = '52657';
                    create index defid_2ndary_ix on eval_idx_tab_II (defID);
                    
                         eval_catt VARCHAR2(50) path ''@CATEGORY''');
                         
                    create index eval_catt_2ndary_ix on eval_idx_tab_I (eval_catt);
                    The xmlindex is getting picked up but a couple of problesm:

                    1. In the developemnt environment, no xml source records for defid '52657' or '52599' are being displayed - just an empty output set occurs; in spite of these values being present and stored in the source xml.
                    This really has me stumped, as can query the eval table to see the actual xml defid vaues '52657' and '52599' exist. Something appears off with the query - which didn't return records even before the corrresponding xml index was created.

                    2. The query still performs slowly, in spite of using the xmlindex. The execution plan shows a full table scan of eval occurring whether or not a HASH JOIN or MERGE JOIN (gets used in place of the HASH JOIN when the NO_USE_HASH(eval) int is used.

                    3. Single column 2ndary indexes created respectively for eval_catt and defid are not used in the execution plan - which may be expected upon further consideration.

                    In the process of running stats at this moment, to see if performance improves....

                    At this point, really after why item '1.' is occurring?

                    Edited by: RickBlanchardSRSCigna on Apr 16, 2012 1:33 PM
                    • 7. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                      odie_63
                      Hi Rick,

                      You didn't post the DDL for the XMLIndex you're finally using. Could you add it?
                      • 8. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                        925561
                        odie 63, et. al.;

                        The statistics completed. Not much performance improvement with or without the no hash hint being used. The 2ndary indexes are not being used.
                        Still not retrieving extant rows.

                        Resolving why the rows are not retrieving seems imperative.
                        Any assistance much appreciated.
                        Regards,
                        Rick Blanchard
                        • 9. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                          925561
                          odie 63,

                          Here it is:
                          CREATE INDEX "EVAL_XMLINDEX_IX" ON "EVAL" (OBJECT_VALUE)
                            INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS
                            ('XMLTable eval_idx_tab_I XMLNamespaces(''http://www.cigna.com/acme/domains/derived/fact/2010/03'' AS "ns7",
                              DEFAULT ''http://www.cigna.com/acme/domains/eval/2010/03''),''/eval'' 
                                 COLUMNS
                                    eval_catt VARCHAR2(50) path ''@category'',
                                    derivedFact XMLTYPE path ''/derivedFacts/ns7:derivedFact'' virtual
                             XMLTable eval_idx_tab_II XMLNamespaces(''http://www.cigna.com/acme/domains/derived/FACT/2010/03'' AS "ns7",
                             DEFAULT ''http://www.cigna.com/acme/domains/eval/2010/03''),''/ns7:derivedFact'' passing eval_xml.derivedFact
                                 COLUMNS 
                                    defId VARCHAR2(100) path ''/derivedFacts/ns7:derivedFact/ns7:defId''');
                          Think accidently, posted the select statement twice...
                          • 10. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                            odie_63
                            The XMLIndex definition is not correct, for the following reasons :

                            In the COLUMNS clause, the PATH expression is relative to the context item coming from the main XQuery expression (here '/eval').
                            If you use an initial slash like you did, it indicates the context item is a "derivedFacts" element (which is not correct since it is an "eval" element).
                            Consequently, the path points to no existing node :
                            derivedFact XMLTYPE path ''/derivedFacts/ns7:derivedFact'' virtual
                            There's a mismatch between the two ns7 prefix declarations, probably a typo?
                            ''http://www.cigna.com/acme/domains/derived/fact/2010/03'' AS "ns7"
                            ...
                            ''http://www.cigna.com/acme/domains/derived/FACT/2010/03'' AS "ns7"
                            What does "eval_xml" refer to here?
                            passing eval_xml.derivedFact
                            The path to "defId" is wrong too :
                            defId VARCHAR2(100) path ''/derivedFacts/ns7:derivedFact/ns7:defId''
                            Try this one, it should get you closer :
                            CREATE INDEX "EVAL_XMLINDEX_IX" ON "EVAL" (OBJECT_VALUE)
                            INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS (
                            'XMLTable eval_idx_tab_I 
                              XMLNamespaces(''http://www.cigna.com/acme/domains/derived/fact/2010/03'' AS "ns7", 
                                            DEFAULT ''http://www.cigna.com/acme/domains/eval/2010/03''),
                              ''/eval'' 
                              COLUMNS
                               eval_catt   VARCHAR2(50) path ''@category'',
                               derivedFact XMLTYPE      path ''derivedFacts/ns7:derivedFact'' virtual
                             XMLTable eval_idx_tab_II 
                              XMLNamespaces(''http://www.cigna.com/acme/domains/derived/fact/2010/03'' AS "ns7",
                                            DEFAULT ''http://www.cigna.com/acme/domains/eval/2010/03''),
                              ''/ns7:derivedFact'' passing derivedFact
                              COLUMNS 
                               defId VARCHAR2(100) path ''ns7:defId'''
                            );
                            • 11. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                              Marco Gralike
                              1. In the developemnt environment, no xml source records for defid '52657' or '52599' are being displayed - just an empty output set occurs; in spite of these values being present and stored in the source xml.
                              This really has me stumped, as can query the eval table to see the actual xml defid vaues '52657' and '52599' exist. Something appears off with the query - which didn't return records even before the corrresponding xml index was created.
                              As Odie hinted, the reason is actually very simple, although you could reason why, or if, the optimizer shouldn't do a second pass against the second content. I am still in doubt if it should or shouldn't or if there is a smart way of dealing with such a condition like the following.

                              The reason no data pops up because the statement used to create the XMLIndex is faulty in such a way that it doesn't produce any data/result set. In such a case the content table doesn't contain any values for the requested data (or even be completely empty). The optimizer will only check the content table / structured xmlindex structure, and will come up empty. This is one of the reasons I always tell people to check and double check if the XML statement used for the XMLIndex creation is correct. This happened me (and others) multiple times. If you are aware of this phenomenon, the error made is easy to find and correctable (most of the time if the data indexed is not massive).
                              • 12. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                                925561
                                Marco,

                                FYI:

                                This morning, before cleaning up the xmlindex per odie 63's suggestions, I altered the xmlindex to invisible (alter index <name> invisible). The optimizer then does not see the xmlindex. Upon running the select statement, no records appeared. When I corrected the path portion in the select statment - similar to what happened in the create xmlindex statement - the records appeared upon running the select statement.

                                Upon altering the incorectly constructed xmlindex to visible, running the select statement ran fine - except slow. My hunch is the explain plan shows the index being considered - reflecting the compile time situation. At runtime, the i ndex doesn't get used by the optimizer (as no node exists due to the malformed xmlindex) or gets accessed but then the optimizer swithes to a full table scan to obtain the xml records.

                                Looks like the optimizer for malformed structured xmlindexes against binary xmltype tables will at least return xml records, albeit inefficiently...

                                Hope this helps. Seems like learning something new everyday using xml db. Wish these behaviors were better documented...

                                Regards,
                                Richard Blanchard
                                • 13. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                                  925561
                                  odie 63,

                                  Once again, feel like a neophyte working wiith XML db... When cleaned up the xmlindex and corrected the path in the sql, the xmlindex proceeded to be used. The explain plan showed a fast full scan occurring on one of the xml index components, also both content tables of the xmlindex were used. The full table scan went away in favor if index scans. The cost went way down 332 vs 788,876,320. The cardinality went down from 4,322,173,878 to 449,797. Hoping this will scale and permit reducing reliance on a proliferation of predefined virtual column usage in favor of more comprehensive xmlindex usage defined when the binary xmltype table was created.

                                  I recreated the two secondary indexes, but found they were not used - probably as expected. Given the nonselectivity of the sql predicate and appropriate usage of the xmlindex components. There was also a nice fast full index scan on a sys_coo415365 - a unique normal index against a sys_nc_oid$ column associated with the eval binary xmltype table. Never say this fast full index scan occuring until the xmlindex functioned properly - not sure if this is an xmllindex component or an artifact of the eval object table.

                                  Thnak-you again for your time and patience.

                                  Regards,
                                  Richard Blanchard
                                  • 14. Re: Why Isn't xmlindex being used in slow query on binary xml table eval?
                                    Marco Gralike
                                    Hope this helps.
                                    Not really, without the execution plans and statements...
                                    1 2 Previous Next