14 Replies Latest reply: Apr 3, 2012 4:01 PM by 924923 RSS

    Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?

    924923
      On our Oracle Database 11g Enterprise Edition Release 11.2.0.1.0, we have a query against against a 25,214 record XMLTYPE ACME_CUST table.
      SELECT rownum   AS seq,
            EID  AS eid,
            SUBSTR(CUST_ID, 1, INSTR(CUST_ID, '|')-1) AS tgt_acme_customer_id,
            SUBSTR(CUST_ID, INSTR(CUST_ID, '|')   +1) AS src_acme_customer_id_list
          FROM
            (SELECT ac.eid EID,
              listagg(ac.acme_cust_id, '|') WITHIN GROUP (
            ORDER BY ac.acme_cust_id, ac.acme_cust_id) CUST_ID
            FROM ACME_CUST ac
            GROUP BY ac.eid
            HAVING COUNT(ac.acme_cust_id)>1)
      Explain plan shows:

      Select Statement
      Count
      VIEW
      FILTER
      Filter Predicates
      COUNT(*) > 1
      SORT GROUP BY
      TABLE ACCESS ACME_CUST FULL

      The ACME_CUST Table has a virtual column defined on acme_cust_id along with a corresponding index. This filed is also defined as a primary key.

      Here is the table definitiion and associated statements:
      CREATE
        TABLE "N98991"."ACME_CUST" OF XMLTYPE
        (
          CONSTRAINT "ACME_CUST_ID_PK" PRIMARY KEY ("ACME_CUST_ID") USING INDEX
          PCTFREE 10 INITRANS 2 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_DEV" ENABLE
        )
        XMLTYPE STORE AS SECUREFILE BINARY XML
        (
          TABLESPACE "ACME_DEV" ENABLE STORAGE IN ROW CHUNK 8192 CACHE READS LOGGING
          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
        (
          "EID" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/customer/customerprofile/2011/11"; (::)                               
      /customerProfile/@eid'
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(15))),
        *bold*  "ACME_CUST_ID" AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY(
          'declare default element namespace "http://www.cigna.com/acme/domains/customer/customerprofile/2011/11"; (::)                               
      /customerProfile/@id' *bold*
          PASSING BY VALUE SYS_MAKEXML(128,"XMLDATA") RETURNING CONTENT ),0,0,
          16777216,0),50,1,2) AS VARCHAR2(50))),
          "CRET_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/customer/customerprofile/2011/11"; (::)                                                                                                       
      /customerProfile/@create_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)))
        )
        PCTFREE 10 PCTUSED 40 INITRANS 1 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_DEV" ;
      CREATE
        INDEX "N98991"."ACME_CST_CRET_DT_IDX" ON "N98991"."ACME_CUST"
        (
          "CRET_DT"
        )
        PCTFREE 10 INITRANS 2 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_DEV" ;
      CREATE
        INDEX "N98991"."ACME_CST_EID_IDX" ON "N98991"."ACME_CUST"
        (
          "EID"
        )
        PCTFREE 10 INITRANS 2 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_DEV" ;
      *bold*CREATE UNIQUE INDEX "N98991"."ACME_CUST_ID_PK" ON "N98991"."ACME_CUST"
        (
          "ACME_CUST_ID"
        )
        PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE *bold*
        (
          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_DEV" ;
        CREATE
          INDEX "N98991"."ACME_CUST_XMLINDEX_IX" ON "N98991"."ACME_CUST"
          (
            OBJECT_VALUE
          )
          INDEXTYPE IS "XDB"."XMLINDEX" PARAMETERS
          (
            'XMLTABLE ACME_CUST_IDX_TAB XMLNamespaces (''http://www.cigna.com/acme/domains/commoncontact/2011/11'' as "cm",  default ''http://www.cigna.com/acme/domains/customer/customerprofile/2011/11''),       
      ''/customerProfile''        
      columns       
      DOB date  PATH ''personInformation/cm:birthDate'',       
      FIRSTNAME varchar2(40)    PATH ''name/cm:givenName'',       
      LASTNAME varchar2(40)    PATH ''name/cm:surName'',       
      SSN varchar2(30)    PATH ''identifiers/ssn'',       
      MEMBERINFOS XMLType path ''memberInfos/memberInfo'' VIRTUAL        
      XMLTable acme_cust_lev2_idx_tab XMLNAMESPACES(default ''http://www.cigna.com/acme/domains/customer/customerprofile/2011/11''),       
      ''/memberInfo'' passing MEMBERINFOS          
      columns          
      ami varchar2(40) PATH ''ami'',         
      subscId varchar2(50) PATH ''clientRelationship/subscriberInformation/subscriberId'',         
      employeeId varchar2(50) PATH ''systemKeys/employeeId'',         
      clientId varchar2(50) PATH ''clientRelationship/clientId''       
      '
          );
      CREATE UNIQUE INDEX "N98991"."SYS_C00384339" ON "N98991"."ACME_CUST"
        (
          "SYS_NC_OID$"
        )
        PCTFREE 10 INITRANS 2 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_DEV" ;
      CREATE UNIQUE INDEX "N98991"."SYS_IL0000649948C00003$$" ON "N98991"."ACME_CUST"
        (
          PCTFREE 10 INITRANS 2 MAXTRANS 255 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_DEV" PARALLEL (DEGREE 0 INSTANCES 0) ;
      Why isn't the unique index ACME_CUST_ID_PK on the virtual column ACME_CUST_ID being used in the explain plan?

      Any input would be much appreciated, as really stumped here.

      Regards,
      Rick
        • 1. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
          odie_63
          Why isn't the unique index ACME_CUST_ID_PK on the virtual column ACME_CUST_ID being used in the explain plan?
          I don't see any reason why it should use it.
          The query doesn't have any predicate, all rows must be visited anyway.

          You were probably thinking that an INDEX_JOIN operation might occur?
          But obviously, assuming it's possible, scanning the two separate indexes (ACME_CST_EID_IDX and ACME_CUST_ID_PK), hash-joining and sorting them is more costly than reading whole blocks of the table at once.

          Why do you think the optimizer should pick this index?
          • 2. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
            924923
            Thank-you for responding - much appreciated.

            My line of thinking was an index scan would be used in this 'nested' query part of this query;
            listagg(ac.acme_cust_id,'|') WITHIN GROUP (
            ORDER BY ac.acme_cust_id,ac.acme_cust_id) CUST_ID
            FROM ACME_CUST ac
            Though there are no predicates involved, the acme_cust_id primary key is all that's involved in this 'nested' query. Given in general,
            scanning index blocks is faster than scanning table blocks; was hoping a full index scan(s) on the ORDER BY clause would occur.
            A Nested Join crossed my mind; but for some reason I was dwelling on Full Index Scan.

            Ref the Oracle documentation (11g Rel2, 'Performance Tuning Guide', E16638-06, 'The Query Optimizer', pg 11-19)

            Here's the snippet from the documentation:

            "Full Scans
            A full index scan eliminates a sort operation, because the data is ordered by the index
            key. It reads the blocks singly. Oracle Database may use a full scan in any of the
            following situations:
            ■ An ORDER BY clause that meets the following requirements is present in the query:
            – All of the columns in the ORDER BY clause must be in the index.
            – The order of the columns in the ORDER BY clause must match the order of the
            leading index columns.
            The ORDER BY clause can contain all of the columns in the index or a subset of the
            columns in the index "

            So was hoping to see this virtual column index used - even though it's a FBI.
            Using the 1g XML features on this engagement - particularly virtual columns in place of direct XMLIndex usage is new uncharted territory for me.

            Regards,
            Richard Blanchard
            • 3. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
              odie_63
              Hi Richard,
              Here's the snippet from the documentation:
              Ok, I see.
              This part refers to the ORDER BY clause terminating a query (the one used to order the final result set), not to ORDER BY clauses that are part of aggregate functions syntax.
              • 4. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                924923
                The documentation does appear vague by not qualifying the 'ORDER BY clause' usage.
                Was hoping could get the virtual column used instead of the FTS.

                W/O anybody coming up with a good rabbit out oof the proverbial hat, will try hinting with /*+ INDEX_FFS (index_name) */.
                Probably a long shot, but all I got.

                Any SWAGS would'nt hurt.


                Thank-you for your time.

                Regards,
                Rick Blanchard
                • 5. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                  odie_63
                  Sorry if I miss the point but again, why do you absolutely need that index to be used?
                  I think forcing a FFS will just be worse.

                  The ultimate proof : run a CBO trace (events 10053) to see why the optimizer chooses a FTS.
                  • 6. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                    924923
                    Sorry if I miss the point but again, why do you absolutely need that index to be used?
                    I think forcing a FFS will just be worse.

                    The ultimate proof : run a CBO trace (events 10053) to see why the optimizer chooses a FTS.



                    The query as it presently runs, may be too slow to use in our production environment.

                    Tried the /*+ INDEX_FFS(ACME_CUST_ID_PK) */ hint in the 'nested' query. Nothing changed,
                    execution plan depicted by the explain plan still shows a FTS not using the index.

                    The 10053 event appears overkill for this situation. May just have to accept the query performance as it is...

                    Regarfds,
                    Richard
                    • 7. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                      Marco Gralike
                      RickBlanchardSRS wrote:
                      Thank-you for responding - much appreciated.

                      My line of thinking was an index scan would be used in this 'nested' query part of this query;
                      listagg(ac.acme_cust_id,'|') WITHIN GROUP (
                      ORDER BY ac.acme_cust_id,ac.acme_cust_id) CUST_ID
                      FROM ACME_CUST ac
                      Though there are no predicates involved, the acme_cust_id primary key is all that's involved in this 'nested' query. Given in general,
                      scanning index blocks is faster than scanning table blocks; was hoping a full index scan(s) on the ORDER BY clause would occur.
                      A Nested Join crossed my mind; but for some reason I was dwelling on Full Index Scan.

                      Ref the Oracle documentation (11g Rel2, 'Performance Tuning Guide', E16638-06, 'The Query Optimizer', pg 11-19)

                      Here's the snippet from the documentation:

                      "Full Scans
                      A full index scan eliminates a sort operation, because the data is ordered by the index
                      key. It reads the blocks singly. Oracle Database may use a full scan in any of the
                      following situations:
                      ■ An ORDER BY clause that meets the following requirements is present in the query:
                      – All of the columns in the ORDER BY clause must be in the index.
                      – The order of the columns in the ORDER BY clause must match the order of the
                      leading index columns.
                      The ORDER BY clause can contain all of the columns in the index or a subset of the
                      columns in the index "

                      So was hoping to see this virtual column index used - even though it's a FBI.
                      Using the 1g XML features on this engagement - particularly virtual columns in place of direct XMLIndex usage is new uncharted territory for me.

                      Regards,
                      Richard Blanchard
                      What I don't get is why you think you are dealing with relational stuff (referencing a performance guide that is based on dealing with relational content),
                      in all you are dealing with XML and not the standard relational database data.

                      Marco/Odie has a point in saying, why not to try to see what the optimizer thinks is the more optimum way by setting the event and see for yourself.
                      In all, while dealing with virtual columns on XML content, the optimizer might have an alternative decision tree that favors the current outcome while dealing with this kind of mixed relational/XML content. And yes, the optimizer might be also wrong.

                      Edited by: Marco Gralike on Mar 24, 2012 12:32 AM
                      • 8. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                        odie_63
                        Hi Richard,
                        The 10053 event appears overkill for this situation.
                        What's the big deal?
                        Set the event, run the query, unset the event, check the trace file, that's all.

                        It's not overkill if it helps you understanding what happens and why an index is of no use in this situation.
                        Tried the /*+ INDEX_FFS(ACME_CUST_ID_PK) */ hint in the 'nested' query.
                        Not sure what nested query you're referring to, so if I misunderstood what you mean, just ignore the following comment.
                        From what you posted earlier, it looks like you're talking about this part :
                        listagg(ac.acme_cust_id,'|') WITHIN GROUP (
                        ORDER BY ac.acme_cust_id,ac.acme_cust_id) CUST_ID
                        That's not a nested query, it's a projection. All the main work (retrieving rows) has already been done when it comes to this part.

                        May just have to accept the query performance as it is...
                        Maybe you can try something else.

                        See the document : Oracle XML DB : Best Practices, page 15 ex. 8 :
                        When there are multiple scalar values that need to be grouped or ordered, it is better to write it
                        with XMLTable construct that projects out all columns to be ordered or grouped as shown
                        below.
                        Here's an example close to your actual requirement :
                        Connected to:
                        Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                        
                        SQL> create table xtab_cols of xmltype
                          2  xmltype store as securefile binary xml;
                        
                        Table created.
                        
                        SQL> insert /*+ append */ into xtab_cols
                          2  select xmlelement("ROW",
                          3           xmlforest(
                          4            TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
                          5            DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
                          6            DEFAULT_LENGTH, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
                          7            DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
                          8            CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
                          9            GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
                         10            V80_FMT_IMAGE, DATA_UPGRADED, HISTOGRAM
                         11           )
                         12         )
                         13  from dba_tab_cols
                         14  where owner = 'SYS'
                         15  ;
                        
                        57079 rows created.
                        
                        SQL> commit;
                        
                        Commit complete.
                        
                        SQL> set long 1000
                        SQL> set pages 100
                        SQL> select xmlserialize(document object_value) from xtab_cols where rownum = 1;
                        
                        XMLSERIALIZE(DOCUMENTOBJECT_VALUE)
                        --------------------------------------------------------------------------------
                        <ROW>
                          <TABLE_NAME>ACCESS$</TABLE_NAME>
                          <COLUMN_NAME>D_OBJ#</COLUMN_NAME>
                          <DATA_TYPE>NUMBER</DATA_TYPE>
                          <DATA_LENGTH>22</DATA_LENGTH>
                          <NULLABLE>N</NULLABLE>
                          <COLUMN_ID>1</COLUMN_ID>
                          <NUM_DISTINCT>7454</NUM_DISTINCT>
                          <LOW_VALUE>C2083A</LOW_VALUE>
                          <HIGH_VALUE>C3031D18</HIGH_VALUE>
                          <DENSITY>,000134156157767642</DENSITY>
                          <NUM_NULLS>0</NUM_NULLS>
                          <NUM_BUCKETS>1</NUM_BUCKETS>
                          <LAST_ANALYZED>2012-01-28</LAST_ANALYZED>
                          <SAMPLE_SIZE>34794</SAMPLE_SIZE>
                          <GLOBAL_STATS>YES</GLOBAL_STATS>
                          <USER_STATS>NO</USER_STATS>
                          <AVG_COL_LEN>5</AVG_COL_LEN>
                          <CHAR_LENGTH>0</CHAR_LENGTH>
                          <V80_FMT_IMAGE>NO</V80_FMT_IMAGE>
                          <DATA_UPGRADED>YES</DATA_UPGRADED>
                          <HISTOGRAM>NONE</HISTOGRAM>
                        </ROW>
                        
                        
                        SQL> exec dbms_stats.gather_table_stats(user, 'XTAB_COLS');
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> set autotrace traceonly
                        SQL> set timing on
                        SQL> set lines 120
                        SQL> select x.table_name
                          2       , listagg(x.column_name, ',') within group (order by column_id)
                          3  from xtab_cols t
                          4     , xmltable('/ROW' passing t.object_value
                          5        columns table_name  varchar2(30) path 'TABLE_NAME'
                          6              , column_name varchar2(30) path 'COLUMN_NAME'
                          7              , column_id   number       path 'COLUMN_ID'
                          8       ) x
                          9  group by x.table_name
                         10  ;
                        
                        4714 rows selected.
                        
                        Elapsed: 00:00:08.25
                        
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 602782846
                        
                        ---------------------------------------------------------------------------------
                        | Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT    |           |   466M|   101G|  1580K  (3)| 05:16:04 |
                        |   1 |  SORT GROUP BY      |           |   466M|   101G|  1580K  (3)| 05:16:04 |
                        |   2 |   NESTED LOOPS      |           |   466M|   101G|  1552K  (1)| 05:10:32 |
                        |   3 |    TABLE ACCESS FULL| XTAB_COLS | 57079 |    12M|   408   (1)| 00:00:05 |
                        |   4 |    XPATH EVALUATION |           |       |       |            |          |
                        ---------------------------------------------------------------------------------
                        
                        
                        Statistics
                        ----------------------------------------------------------
                                  9  recursive calls
                                  1  db block gets
                               1713  consistent gets
                                  0  physical reads
                                 96  redo size
                             773516  bytes sent via SQL*Net to client
                               3873  bytes received via SQL*Net from client
                                316  SQL*Net roundtrips to/from client
                                  1  sorts (memory)
                                  0  sorts (disk)
                               4714  rows processed
                        And of course, even better after adding a structured XML index (4714 rows fetched in 1s) :
                        SQL> CREATE INDEX xtab_cols_sxi ON xtab_cols (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex
                          2  PARAMETERS (
                          3  q'#XMLTable my_xtab
                          4  '/ROW'
                          5  columns table_name varchar2(30) path 'TABLE_NAME'
                          6        , column_name varchar2(30) path 'COLUMN_NAME'
                          7        , column_id number path 'COLUMN_ID' #');
                        
                        Index created.
                        
                        Elapsed: 00:00:13.42
                        SQL> select x.table_name
                          2       , listagg(x.column_name, ',') within group (order by column_id)
                          3  from xtab_cols t
                          4     , xmltable('/ROW' passing t.object_value
                          5        columns table_name  varchar2(30) path 'TABLE_NAME'
                          6              , column_name varchar2(30) path 'COLUMN_NAME'
                          7              , column_id   number       path 'COLUMN_ID'
                          8       ) x
                          9  group by x.table_name
                         10  ;
                        
                        4714 rows selected.
                        
                        Elapsed: 00:00:01.00
                        
                        Execution Plan
                        ----------------------------------------------------------
                        Plan hash value: 3303494605
                        
                        ------------------------------------------------------------------------------
                        | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
                        ------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT   |         | 57520 |  3201K|   174   (3)| 00:00:03 |
                        |   1 |  SORT GROUP BY     |         | 57520 |  3201K|   174   (3)| 00:00:03 |
                        |   2 |   TABLE ACCESS FULL| MY_XTAB | 57520 |  3201K|   171   (1)| 00:00:03 |
                        ------------------------------------------------------------------------------
                        
                        Note
                        -----
                           - dynamic sampling used for this statement (level=2)
                        
                        
                        Statistics
                        ----------------------------------------------------------
                                297  recursive calls
                                  1  db block gets
                                989  consistent gets
                                  0  physical reads
                                176  redo size
                             773516  bytes sent via SQL*Net to client
                               3873  bytes received via SQL*Net from client
                                316  SQL*Net roundtrips to/from client
                                 21  sorts (memory)
                                  0  sorts (disk)
                               4714  rows processed
                        • 9. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                          924923
                          odie 63,

                          It's clear the optimizer won't use your XML Index's content table or the the virtual column's index in my example.
                          Upon reviewing fast full sindex scans in the metalink documentation; didn't appear the index scan would be used in the 'nested' (probably would've been better to address as the 'inner' query portion of the whole query. In an old metalink note, there appeared a qualification all referenced columns in select list are part of the index - such is not the case here. Also, the fast full scan would not have been ordered even if used - you had alluded to the ffs would not be any good even if it was used.

                          Without a predicate in the outer query there appears no basis for other than a FTS - as you pointed out.

                          Liked your example using the XMLTable instead of a virtual column.

                          You've been very helpful in bringing closureof htis post . My second post on "Why Isn't XMLIndex Not Being Used In Slow Query Against an XML Type Table?" has a very selective predicate which in my way of thinking would use the XMLIndex.

                          Thank-you again.
                          Regards,
                          Rick Blanchard
                          • 10. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                            924923
                            Marco Gralike,

                            When it comes to performance considerations in this XML DB, I heavily use the Oracle XML DB Developer's Guide 11g Release 2 along with the relational centric Oracle Performance Tuning Guide, Metalink, and google hits. Our main problem with this XML DB environment centers around our inability to adequate performance on many queries.

                            In this particular query, looks like I was mistaken in casually expecting an index on a virtual column against an XML source table would be used. Using the 10053 trace probably would've given some good information if wasn't making headway on why the optimizer wasn't using the index. Normally, I''d just go ahead and produce the trace, but obtaining the file would be difficult in this environment. I was more after understanding what was going on in this SQL.

                            As it turns out, Odie gave some very good pointers on this situation. I need to check out that XML Best practices guide Odie spoke of.

                            Regards,
                            Rick Blanchard
                            • 11. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                              Marco Gralike
                              I just wanted to point out that it is dangerous to assume that the internal workings of an Global Index like the XMLIndex (structured/unstructured) are equal in behavior as thing described in the performance guide, which is mainly based on a relational environment. The XML domain has its own query engines that might or might not follow the rules of the relational domain. Oracle will only do this when it makes clearly sense and optimizes the end result. Sometimes the optimizer is also not aware of everything what is going on in this domain and/or can't yet optimize this (an example jumps in my mind regarding secondary indexes on structured XMLIndexes/content tables). Sometimes its just a bug.
                              • 12. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                                924923
                                Marco,

                                Points well taken. Not to digress too much; but did you have trouble using 2ndary indexes on structured XMLIndex content tables? I've been unable to get 2ndary indexes used in our content tables and have to research this more. Also appears may be a limitation in the number of 2ndary indexes that can be created in 11.2.0.1.

                                Rick Blanchard
                                • 13. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                                  Marco Gralike
                                  Hmmm wasn't aware of
                                  Also appears may be a limitation in the number of 2ndary indexes that can be created in 11.2.0.1.
                                  Until now all my secondary indexes were picked up by the optimizer when they made sense. Would be interested in an example to test with regarding the limitation issue.
                                  • 14. Re: Why Is Query Against XMLTYPE Table ACME_CUST Doing A Full Table Scan?
                                    924923
                                    Marco,

                                    I miay have spoken too loosely...

                                    Before I arrived on this engagement, my client's DBA group issued an SR against release 11.2.0.1. After some discussion and code submittal over this SR, Oracle Support could not reproduce a 5 2ndary index creation limit experienced against a structured XMLIndex. Oracle suggested upgrading to 11.2.0.3. So this purported 2ndary Index creation limit wasn't conclusively validated via Oracle Support.

                                    Reference odie 63's discussion in the 'Why Isn't XMLIndex Not Being Used In Slow Query Against an XMLType Table?' post. Discusses situations where the 2ndary index doesn't get used.

                                    Regards,
                                    Rick Blanchard

                                    Edited by: RickBlanchardSRS on Apr 3, 2012 4:54 PM