This discussion is archived
5 Replies Latest reply: Oct 4, 2013 4:23 AM by odie_63 RSS

Cardinality explosion with several XMLTable calls in one query - how to constrain ?

user10214810 Newbie
Currently Being Moderated

Hi,

 

when I engage several (say, four) XMLTable calls using comma (= cartesian join) in a single query, the plan shows me insane cost of 15 digits. Though execution time is reasonable because XML document is very small, I would like the plan appear with more realistic values to correctly predict query time when XML document becomes larger. How can I make CBO be aware of actual size of XML text?

Should I use XML indexing to constrain search space? What are the 'best practices' in indexing XML text for efficient parsing by XMLTable?

 

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production


Thanks for your answers!

  • 1. Re: Cardinality explosion with several XMLTable calls in one query - how to constrain ?
    Jason_(A_Non) Expert
    Currently Being Moderated

    This is not directly answering your question, but when you say multiple XMLTable calls in a cartesian join, are you referring to pulling XML from multiple tables or multiple XMLTable calls into the same XML?

     

    How is the XML stored?

     

    What does a representative SQL and Explain plan look like?

  • 2. Re: Cardinality explosion with several XMLTable calls in one query - how to constrain ?
    user10214810 Newbie
    Currently Being Moderated

    Hello,

     

    I use the very same approach as described in the How To : Load XML data into multiple tables | Odie's Oracle Blog (I believe you suggested it in another post here). My SELECT is similar, XML text is similar.

     

    AFAIK, in the following query (XMLBIN is a table of XMLTYPE containing  the document) joining

     

    SELECT * FROM XMLBIN

    ,XMLTable (....)

    ,XMLTable (....)

    ,XMLTable (....)

    ,XMLTable (....)

     

    without any join criteria means cartesian join, which appear in the plan as multiple nested loops.

    I shall post the plan shortly,

     

    Thanks.

  • 3. Re: Cardinality explosion with several XMLTable calls in one query - how to constrain ?
    odie_63 Guru
    Currently Being Moderated

    when I engage several (say, four) XMLTable calls using comma (= cartesian join) in a single query, the plan shows me insane cost of 15 digits.

     

    The cost figure is irrelevant in this situation (as it is in most situation I might add).

    That's because the CBO cannot estimate the cardinality of the virtual tables, among other things.

    Don't focus on it.

     

    To get a more realistic estimate, you have to use structured (OR) storage, or an XMLIndex.

    The kind of index to use depends on your query, but if you say your use case is similar to the one in the blog post then I'd go with a structured XMLIndex.

     

    See this document for more information on recommended approaches (applies to 11.2 as well for the most part) :

    http://www.oracle.com/technetwork/database-features/xmldb/overview/xmldb-bpwp-12cr1-1964809.pdf

     

    without any join criteria means cartesian join, which appear in the plan as multiple nested loops.

    If the XMLTABLEs are chained together via their PASSING clauses, then those are considered as join predicates.

  • 4. Re: Cardinality explosion with several XMLTable calls in one query - how to constrain ?
    user10214810 Newbie
    Currently Being Moderated

    Hi,

     

    Thanks, odie_63. I used index to give CBA an idea of cardinality.

     

    So, the original plan is (P=Peta, T=Tera, etc...):

    Plan hash value: 954188411

    
    -------------------------------------------------------------------------------------
    | Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |             |  4451T|  5099P|    14T  (1)|999:59:59 |
    |   1 |  NESTED LOOPS         |             |  4451T|  5099P|    14T  (1)|999:59:59 |
    |   2 |   NESTED LOOPS        |             |   544G|   638T|  1816M  (1)|999:59:59 |
    |   3 |    NESTED LOOPS       |             |    66M|    79G|   222K  (1)| 00:44:34 |
    |   4 |     NESTED LOOPS      |             |  8168 |     9M|    32   (0)| 00:00:01 |
    |   5 |      TABLE ACCESS FULL| XML_DROPBOX |     1 |  1252 |     3   (0)| 00:00:01 |
    |   6 |      XPATH EVALUATION |             |       |       |            |          |
    |   7 |     XPATH EVALUATION  |             |       |       |            |          |
    |   8 |    XPATH EVALUATION   |             |       |       |            |          |
    |   9 |   XPATH EVALUATION    |             |       |       |            |          |
    -------------------------------------------------------------------------------------
      
    Note
    -----
       - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

    After creating an index, like:

     

    CREATE INDEX XML_DROPBOX_IDX  ON XML_DROPBOX (SYS_NC_ROWINFO$)
      INDEXTYPE IS XDB.XMLINDEX PARAMETERS (
      'PATH TABLE XML_DROPBOX_PATH_IDX (NOLOGGING ENABLE ROW MOVEMENT)
       PATH ID INDEX XML_DROPBOX_PATH_ID_IDX (NOLOGGING)'
    );

     

    the plan become more voluminous, but shows realistic cost:

     

    Plan hash value: 1726871192
    --------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
    --------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                         |     1 | 10666 |     9   (0)| 00:00:01 |
    |*  1 |  FILTER                            |                         |       |       |            |          |
    |*  2 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |*  4 |  FILTER                            |                         |       |       |            |          |
    |*  5 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |*  6 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |*  7 |  FILTER                            |                         |       |       |            |          |
    |*  8 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |*  9 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 10 |  FILTER                            |                         |       |       |            |          |
    |* 11 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 12 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 13 |  FILTER                            |                         |       |       |            |          |
    |* 14 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 15 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 16 |  FILTER                            |                         |       |       |            |          |
    |* 17 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 18 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 19 |  FILTER                            |                         |       |       |            |          |
    |* 20 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 21 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 22 |  FILTER                            |                         |       |       |            |          |
    |* 23 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 24 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 25 |  FILTER                            |                         |       |       |            |          |
    |* 26 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 27 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 28 |  FILTER                            |                         |       |       |            |          |
    |* 29 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 30 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 31 |  FILTER                            |                         |       |       |            |          |
    |* 32 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 33 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 34 |  FILTER                            |                         |       |       |            |          |
    |* 35 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 36 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 37 |  FILTER                            |                         |       |       |            |          |
    |* 38 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 39 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 40 |  FILTER                            |                         |       |       |            |          |
    |* 41 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 42 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 43 |  FILTER                            |                         |       |       |            |          |
    |* 44 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 45 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 46 |  FILTER                            |                         |       |       |            |          |
    |* 47 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 48 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 49 |  FILTER                            |                         |       |       |            |          |
    |* 50 |   TABLE ACCESS BY INDEX ROWID      | XML_DROPBOX_PATH_IDX    |     1 |  3524 |     2   (0)| 00:00:01 |
    |* 51 |    INDEX RANGE SCAN                | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |  52 |  NESTED LOOPS                      |                         |     1 | 10666 |     9   (0)| 00:00:01 |
    |  53 |   NESTED LOOPS                     |                         |     1 | 10654 |     8   (0)| 00:00:01 |
    |  54 |    NESTED LOOPS                    |                         |     1 |  9132 |     7   (0)| 00:00:01 |
    |  55 |     NESTED LOOPS                   |                         |     1 |  7610 |     6   (0)| 00:00:01 |
    |  56 |      NESTED LOOPS                  |                         |     1 |  6088 |     5   (0)| 00:00:01 |
    |  57 |       NESTED LOOPS                 |                         |     1 |  4566 |     4   (0)| 00:00:01 |
    |  58 |        NESTED LOOPS                |                         |     1 |  3044 |     3   (0)| 00:00:01 |
    |* 59 |         TABLE ACCESS BY INDEX ROWID| XML_DROPBOX_PATH_IDX    |     1 |  1522 |     2   (0)| 00:00:01 |
    |* 60 |          INDEX RANGE SCAN          | XML_DROPBOX_PATH_ID_IDX |     1 |       |     1   (0)| 00:00:01 |
    |* 61 |         TABLE ACCESS BY INDEX ROWID| XML_DROPBOX_PATH_IDX    |     1 |  1522 |     1   (0)| 00:00:01 |
    |* 62 |          INDEX RANGE SCAN          | XML_DROPBOX_PATH_ID_IDX |     1 |       |     0   (0)| 00:00:01 |
    |* 63 |        TABLE ACCESS BY INDEX ROWID | XML_DROPBOX_PATH_IDX    |     1 |  1522 |     1   (0)| 00:00:01 |
    |* 64 |         INDEX RANGE SCAN           | XML_DROPBOX_PATH_ID_IDX |     1 |       |     0   (0)| 00:00:01 |
    |* 65 |       TABLE ACCESS BY INDEX ROWID  | XML_DROPBOX_PATH_IDX    |     1 |  1522 |     1   (0)| 00:00:01 |
    |* 66 |        INDEX RANGE SCAN            | XML_DROPBOX_PATH_ID_IDX |     1 |       |     0   (0)| 00:00:01 |
    |* 67 |      TABLE ACCESS BY INDEX ROWID   | XML_DROPBOX_PATH_IDX    |     1 |  1522 |     1   (0)| 00:00:01 |
    |* 68 |       INDEX RANGE SCAN             | XML_DROPBOX_PATH_ID_IDX |     1 |       |     0   (0)| 00:00:01 |
    |* 69 |     TABLE ACCESS BY INDEX ROWID    | XML_DROPBOX_PATH_IDX    |     1 |  1522 |     1   (0)| 00:00:01 |
    |* 70 |      INDEX RANGE SCAN              | XML_DROPBOX_PATH_ID_IDX |     1 |       |     0   (0)| 00:00:01 |
    |* 71 |    TABLE ACCESS BY INDEX ROWID     | XML_DROPBOX_PATH_IDX    |     1 |  1522 |     1   (0)| 00:00:01 |
    |* 72 |     INDEX RANGE SCAN               | XML_DROPBOX_PATH_ID_IDX |     1 |       |     0   (0)| 00:00:01 |
    |  73 |   TABLE ACCESS BY USER ROWID       | XML_DROPBOX             |     1 |    12 |     1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
       2 - filter("SYS_P6"."ORDER_KEY">:B1 AND "SYS_P6"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P6"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)
       3 - access("SYS_P6"."PATHID"=HEXTORAW('6043')  AND "SYS_P6"."RID"=:B1)
       4 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
       5 - filter("SYS_P9"."ORDER_KEY">:B1 AND "SYS_P9"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P9"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P9"."LOCATOR")=1)
       6 - access("SYS_P9"."PATHID"=HEXTORAW('66A6')  AND "SYS_P9"."RID"=:B1)
       7 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
       8 - filter("SYS_P12"."ORDER_KEY">:B1 AND "SYS_P12"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P12"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P12"."LOCATOR")=1)
       9 - access("SYS_P12"."PATHID"=HEXTORAW('4203')  AND "SYS_P12"."RID"=:B1)
      10 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      11 - filter("SYS_P15"."ORDER_KEY">:B1 AND "SYS_P15"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P15"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P15"."LOCATOR")=1)
      12 - access("SYS_P15"."PATHID"=HEXTORAW('66BA')  AND "SYS_P15"."RID"=:B1)
      13 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      14 - filter("SYS_P18"."ORDER_KEY">:B1 AND "SYS_P18"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P18"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P18"."LOCATOR")=1)
      15 - access("SYS_P18"."PATHID"=HEXTORAW('550B')  AND "SYS_P18"."RID"=:B1)
      16 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      17 - filter("SYS_P21"."ORDER_KEY">:B1 AND "SYS_P21"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P21"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P21"."LOCATOR")=1)
      18 - access("SYS_P21"."PATHID"=HEXTORAW('652E')  AND "SYS_P21"."RID"=:B1)
      19 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      20 - filter("SYS_P24"."ORDER_KEY">:B1 AND "SYS_P24"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P24"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P24"."LOCATOR")=1)
      21 - access("SYS_P24"."PATHID"=HEXTORAW('44EC')  AND "SYS_P24"."RID"=:B1)
      22 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      23 - filter("SYS_P31"."ORDER_KEY">:B1 AND "SYS_P31"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P31"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P31"."LOCATOR")=1)
      24 - access("SYS_P31"."PATHID"=HEXTORAW('2C38')  AND "SYS_P31"."RID"=:B1)
      25 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      26 - filter("SYS_P34"."ORDER_KEY">:B1 AND "SYS_P34"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P34"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P34"."LOCATOR")=1)
      27 - access("SYS_P34"."PATHID"=HEXTORAW('3026')  AND "SYS_P34"."RID"=:B1)
      28 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      29 - filter("SYS_P37"."ORDER_KEY">:B1 AND "SYS_P37"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P37"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+2 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P37"."LOCATOR")=1)
      30 - access("SYS_P37"."PATHID"=HEXTORAW('4F1A')  AND "SYS_P37"."RID"=:B1)
      31 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      32 - filter("SYS_P40"."ORDER_KEY">:B1 AND "SYS_P40"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P40"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+2 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P40"."LOCATOR")=1)
      33 - access("SYS_P40"."PATHID"=HEXTORAW('7E65')  AND "SYS_P40"."RID"=:B1)
      34 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      35 - filter("SYS_P43"."ORDER_KEY">:B1 AND "SYS_P43"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P43"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P43"."LOCATOR")=1)
      36 - access("SYS_P43"."PATHID"=HEXTORAW('7497')  AND "SYS_P43"."RID"=:B1)
      37 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      38 - filter("SYS_P46"."ORDER_KEY">:B1 AND "SYS_P46"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P46"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P46"."LOCATOR")=1)
      39 - access("SYS_P46"."PATHID"=HEXTORAW('2D8D')  AND "SYS_P46"."RID"=:B1)
      40 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      41 - filter("SYS_P49"."ORDER_KEY">:B1 AND "SYS_P49"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P49"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P49"."LOCATOR")=1)
      42 - access("SYS_P49"."PATHID"=HEXTORAW('4486')  AND "SYS_P49"."RID"=:B1)
      43 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      44 - filter("SYS_P66"."ORDER_KEY">:B1 AND "SYS_P66"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P66"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P66"."LOCATOR")=1)
      45 - access("SYS_P66"."PATHID"=HEXTORAW('5005')  AND "SYS_P66"."RID"=:B1)
      46 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      47 - filter("SYS_P69"."ORDER_KEY">:B1 AND "SYS_P69"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P69"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P69"."LOCATOR")=1)
      48 - access("SYS_P69"."PATHID"=HEXTORAW('3935')  AND "SYS_P69"."RID"=:B1)
      49 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
      50 - filter("SYS_P59"."ORDER_KEY">:B1 AND "SYS_P59"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B2) AND 
                  SYS_ORDERKEY_DEPTH("SYS_P59"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B3)+1 AND 
                  SYS_XMLI_LOC_ISNODE("SYS_P59"."LOCATOR")=1)
      51 - access("SYS_P59"."PATHID"=HEXTORAW('37CE')  AND "SYS_P59"."RID"=:B1)
      59 - filter(SYS_XMLI_LOC_ISNODE("SYS_P49"."LOCATOR")=1)
      60 - access("SYS_P49"."PATHID"=HEXTORAW('4486') )
      61 - filter(SYS_XMLI_LOC_ISNODE("SYS_P62"."LOCATOR")=1 AND 
                  "SYS_P49"."ORDER_KEY"<"SYS_P62"."ORDER_KEY" AND "SYS_P62"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P49".
                  "ORDER_KEY") AND SYS_ORDERKEY_DEPTH("SYS_P49"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P62"."ORDER_KEY")
                  )
      62 - access("SYS_P62"."PATHID"=HEXTORAW('780B')  AND "SYS_P62"."RID"="SYS_P49"."RID")
      63 - filter(SYS_XMLI_LOC_ISNODE("SYS_P27"."LOCATOR")=1 AND 
                  "SYS_P27"."ORDER_KEY"<"SYS_P49"."ORDER_KEY" AND "SYS_P49"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P27".
                  "ORDER_KEY") AND SYS_ORDERKEY_DEPTH("SYS_P27"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P49"."ORDER_KEY")
                  )
      64 - access("SYS_P27"."PATHID"=HEXTORAW('49C4')  AND "SYS_P49"."RID"="SYS_P27"."RID")
      65 - filter(SYS_XMLI_LOC_ISNODE("SYS_P46"."LOCATOR")=1 AND 
                  "SYS_P27"."ORDER_KEY"<"SYS_P46"."ORDER_KEY" AND "SYS_P46"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P27".
                  "ORDER_KEY") AND SYS_ORDERKEY_DEPTH("SYS_P27"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P46"."ORDER_KEY")
                  )
      66 - access("SYS_P46"."PATHID"=HEXTORAW('2D8D')  AND "SYS_P46"."RID"="SYS_P27"."RID")
      67 - filter(SYS_XMLI_LOC_ISNODE("SYS_P52"."LOCATOR")=1 AND 
                  "SYS_P46"."ORDER_KEY"<"SYS_P52"."ORDER_KEY" AND "SYS_P52"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P46".
                  "ORDER_KEY") AND SYS_ORDERKEY_DEPTH("SYS_P46"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P52"."ORDER_KEY")
                  )
      68 - access("SYS_P52"."PATHID"=HEXTORAW('6AFA')  AND "SYS_P52"."RID"="SYS_P46"."RID")
      69 - filter(SYS_XMLI_LOC_ISNODE("SYS_P24"."LOCATOR")=1 AND 
                  "SYS_P24"."ORDER_KEY"<"SYS_P27"."ORDER_KEY" AND "SYS_P27"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P24".
                  "ORDER_KEY") AND SYS_ORDERKEY_DEPTH("SYS_P24"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P27"."ORDER_KEY")
                  )
      70 - access("SYS_P24"."PATHID"=HEXTORAW('44EC')  AND "SYS_P27"."RID"="SYS_P24"."RID")
      71 - filter(SYS_XMLI_LOC_ISNODE("SYS_P4"."LOCATOR")=1 AND 
                  "SYS_P4"."ORDER_KEY"<"SYS_P24"."ORDER_KEY" AND "SYS_P24"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_P4"."O
                  RDER_KEY") AND SYS_ORDERKEY_DEPTH("SYS_P4"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_P24"."ORDER_KEY"))
      72 - access("SYS_P4"."PATHID"=HEXTORAW('5B3D')  AND "SYS_P24"."RID"="SYS_P4"."RID")
    Note
    -----
       - dynamic sampling used for this statement (level=2)
  • 5. Re: Cardinality explosion with several XMLTable calls in one query - how to constrain ?
    odie_63 Guru
    Currently Being Moderated

    You've used a full blown unstructured index, I don't think it's the wisest idea here. You should at least use path subsetting, or a structured index.

    In some cases, a streaming evaluation is faster than playing yo-yo with the index PATH table.

     

    Maybe that new plan makes you feel more comfortable but is the query faster and consuming less resources?

    A SQL trace file will tell you that, not an explain plan.

Legend

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