This discussion is archived
14 Replies Latest reply: Apr 3, 2012 2:01 PM by 924923 RSS

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

924923 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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