9 Replies Latest reply: Dec 11, 2013 5:06 AM by Marco Gralike RSS

    How to create index on XMLTYPE ordered collection table?

    978225

      I am using Oracle 11.2.0.2.

       

       

      Essentially, my XML documents have a 3-level hierarchy:

      event

        +---action  [1:n]

               +---- param   [1:n]

       

       

       

      I am trying to create indexes on ordered collection tables, but can not get the syntax right...

       

       

      I have created a table with an object-relational XMLType column:

       

      CREATE TABLE T_C_RMP_MNTRNG_XML_FULL_IL4 (
        MESSAGE_ID NUMBER(22,0) NOT NULL ENABLE,
        XML_EVAL_ID NUMBER(22,0),
        VIN7 VARCHAR2(7 BYTE),
        FLEET_ID VARCHAR2(50 BYTE),
        CSC_SW_VERSION VARCHAR2(100 BYTE),
        RECEIVED DATE,
        XML_CONTENT SYS.XMLTYPE ,
        DWH_LM_TS_UTC DATE NOT NULL ENABLE,
        CONSTRAINT PK_C_RMP_MNTRNG_XML_FULL_IL4 PRIMARY KEY (MESSAGE_ID)
      ) NOLOGGING TABLESPACE CATALOG
      VARRAY "XML_CONTENT"."XMLDATA"."action" STORE AS TABLE "T_OR_MON_ACTION" (
        NOLOGGING TABLESPACE "CATALOG"
        VARRAY "param" STORE AS TABLE "T_OR_MON_ACTION_PARAM" (
        NOLOGGING TABLESPACE "CATALOG"
        ) RETURN AS LOCATOR
      ) RETURN AS LOCATOR
      XMLTYPE XML_CONTENT STORE AS OBJECT RELATIONAL XMLSCHEMA "http://mydomain.com/cs.xsd" ELEMENT "monitoring";
      
      
      
      
      

       

       

       

      I am running the following SELECT:

      SELECT EVENT_ID, ACTION_SUB_ID, MESSAGE_ID, ACTION_TYPE, UNIXTS_TO_DATE(ACTION_TIMESTAMP) ACTION_TIMESTAMP
      FROM T_C_RMP_MNTRNG_XML_FULL_IL4, 
      XMLTABLE( 
        'for $i1 in /monitoring , 
        $i2 in $i1/action            
        return element r {              
        $i1/eventId,              
        $i2            
        }' 
        PASSING XML_CONTENT COLUMNS 
        EVENT_ID VARCHAR(40) PATH 'eventId', 
        ACTION_SUB_ID INTEGER PATH 'action/actionSubId', 
        ACTION_TYPE VARCHAR2(100) PATH 'action/type', 
        ACTION_TIMESTAMP NUMBER(13,0) PATH 'action/time' 
      ) T2 
      WHERE ( 
        EVENT_ID IS NOT NULL AND ACTION_SUB_ID IS NOT NULL 
      )
      
      
      
      
      

       

      The explain plan looks like this (sorry, don't know how to get this formatted any "eye-friendlier"):

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

      | Id  | Operation          | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

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

      |   0 | SELECT STATEMENT   |                             |  1609K|  6316M|       |  6110K  (1)| 20:22:11 |

      |*  1 |  HASH JOIN         |                             |  1609K|  6316M|   111M|  6110K  (1)| 20:22:11 |

      |   2 |   TABLE ACCESS FULL| T_C_RMP_MNTRNG_XML_FULL_IL4 |   582K|   104M|       |  5241   (1)| 00:01:03 |

      |*  3 |   TABLE ACCESS FULL| T_OR_MON_ACTION             |    32M|   117G|       |   105K  (2)| 00:21:08 |

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

       

      Predicate Information (identified by operation id):

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

       

         1 - access("NESTED_TABLE_ID"="T_C_RMP_MNTRNG_XML_FULL_IL4"."SYS_NC0001300014$")

             filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(XMLCONCAT(SYS_XMLGEN("T_C_RMP_MNTRN

                    G_XML_FULL_IL4"."SYS_NC00017$",NULL,SYS_XMLCONV("T_C_RMP_MNTRNG_XML_FULL_IL4"."SYS_NC00012$",0,32,

                    'EC1EEF23FD023A27E04032A06D930A8D',3,3783,1)),SYS_MAKEXML('EC1EEF23FD023A27E04032A06D930A8D',3780,

                    "T_C_RMP_MNTRNG_XML_FULL_IL4"."SYS_NC00008$","SYS_ALIAS_0"."SYS_NC_ROWINFO$")),'/eventId',NULL),0,

                    0,20971520,0),50,1,2) AS VARCHAR(40) ) IS NOT NULL)

         3 - filter(CAST(TO_NUMBER(TO_CHAR("SYS_ALIAS_0"."actionSubId")) AS INTEGER ) IS NOT NULL)

       

      Note

      -----

         - dynamic sampling used for this statement (level=2)

         - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

       

      The XML schema looks like this:

      <?xml version="1.0" encoding="UTF-8"?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" xmlns:oraxdb="http://xmlns.oracle.com/xdb" oraxdb:storeVarrayAsTable="true" oraxdb:flags="2105639" oraxdb:schemaURL="http://mydomain.com/cs.xsd" oraxdb:schemaOwner="MYUSER" oraxdb:numProps="23">
        <xs:element name="monitoring" oraxdb:propNumber="3785" oraxdb:global="true" oraxdb:SQLName="monitoring" oraxdb:SQLType="monitoring755_T" oraxdb:SQLSchema="MYUSER" oraxdb:memType="258" oraxdb:defaultTable="monitoring757_TAB" oraxdb:defaultTableSchema="MYUSER">
          <xs:complexType oraxdb:SQLType="monitoring755_T" oraxdb:SQLSchema="MYUSER">
            <xs:sequence>
              <xs:element maxOccurs="unbounded" ref="action" oraxdb:propNumber="3780" oraxdb:global="false" oraxdb:SQLName="action" oraxdb:SQLType="action752_T" oraxdb:SQLSchema="MYUSER" oraxdb:memType="258" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false" oraxdb:SQLCollType="action756_COLL" oraxdb:SQLCollSchema="MYUSER"/>
              <xs:element ref="reservationType" oraxdb:propNumber="3781" oraxdb:global="false" oraxdb:SQLName="reservationType" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
              <xs:element ref="softwareVersion" oraxdb:propNumber="3782" oraxdb:global="false" oraxdb:SQLName="softwareVersion" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
              <xs:element ref="eventId" oraxdb:propNumber="3783" oraxdb:global="false" oraxdb:SQLName="eventId" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
              <xs:element ref="vin" oraxdb:propNumber="3784" oraxdb:global="false" oraxdb:SQLName="vin" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="action" oraxdb:propNumber="3790" oraxdb:global="true" oraxdb:SQLName="action" oraxdb:SQLType="action752_T" oraxdb:SQLSchema="MYUSER" oraxdb:memType="258" oraxdb:defaultTable="action754_TAB" oraxdb:defaultTableSchema="MYUSER">
          <xs:complexType oraxdb:SQLType="action752_T" oraxdb:SQLSchema="MYUSER">
            <xs:sequence>
              <xs:element ref="type" oraxdb:propNumber="3786" oraxdb:global="false" oraxdb:SQLName="type" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
              <xs:element maxOccurs="unbounded" ref="param" oraxdb:propNumber="3787" oraxdb:global="false" oraxdb:SQLName="param" oraxdb:SQLType="param749_T" oraxdb:SQLSchema="MYUSER" oraxdb:memType="258" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false" oraxdb:SQLCollType="param753_COLL" oraxdb:SQLCollSchema="MYUSER"/>
              <xs:element ref="actionSubId" oraxdb:propNumber="3788" oraxdb:global="false" oraxdb:SQLName="actionSubId" oraxdb:SQLType="NUMBER" oraxdb:memType="2" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
              <xs:element ref="time" oraxdb:propNumber="3789" oraxdb:global="false" oraxdb:SQLName="time" oraxdb:SQLType="NUMBER" oraxdb:memType="2" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="type" type="xs:string" oraxdb:propNumber="3791" oraxdb:global="true" oraxdb:SQLName="type" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="type751_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="param" oraxdb:propNumber="3794" oraxdb:global="true" oraxdb:SQLName="param" oraxdb:SQLType="param749_T" oraxdb:SQLSchema="MYUSER" oraxdb:memType="258" oraxdb:defaultTable="param750_TAB" oraxdb:defaultTableSchema="MYUSER">
          <xs:complexType oraxdb:SQLType="param749_T" oraxdb:SQLSchema="MYUSER">
            <xs:sequence>
              <xs:element minOccurs="0" ref="value" oraxdb:propNumber="3792" oraxdb:global="false" oraxdb:SQLName="value" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
              <xs:element ref="key" oraxdb:propNumber="3793" oraxdb:global="false" oraxdb:SQLName="key" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:MemInline="false" oraxdb:SQLInline="true" oraxdb:JavaInline="false"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="value" type="xs:string" oraxdb:propNumber="3795" oraxdb:global="true" oraxdb:SQLName="value" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="value748_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="key" type="xs:string" oraxdb:propNumber="3796" oraxdb:global="true" oraxdb:SQLName="key" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="key747_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="actionSubId" type="xs:integer" oraxdb:propNumber="3797" oraxdb:global="true" oraxdb:SQLName="actionSubId" oraxdb:SQLType="NUMBER" oraxdb:memType="2" oraxdb:defaultTable="actionSubId746_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="time" type="xs:integer" oraxdb:propNumber="3798" oraxdb:global="true" oraxdb:SQLName="time" oraxdb:SQLType="NUMBER" oraxdb:memType="2" oraxdb:defaultTable="time745_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="reservationType" type="xs:string" oraxdb:propNumber="3799" oraxdb:global="true" oraxdb:SQLName="reservationType" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="reservationType744_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="softwareVersion" type="xs:string" oraxdb:propNumber="3800" oraxdb:global="true" oraxdb:SQLName="softwareVersion" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="softwareVersion743_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="eventId" type="xs:string" oraxdb:propNumber="3801" oraxdb:global="true" oraxdb:SQLName="eventId" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="eventId742_TAB" oraxdb:defaultTableSchema="MYUSER"/>
        <xs:element name="vin" type="xs:string" oraxdb:propNumber="3802" oraxdb:global="true" oraxdb:SQLName="vin" oraxdb:SQLType="VARCHAR2" oraxdb:memType="1" oraxdb:defaultTable="vin741_TAB" oraxdb:defaultTableSchema="MYUSER"/>
      </xs:schema>
      
      
      
      
      

      How can I create an index on these ordered collection tables in order to improve performance?

      I found the example at http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_rewrite.htm#ADXDB5859 but am not able to apply it to this particular case...

       

       

      Thank you in advance...

        • 1. Re: How to create index on XMLTYPE ordered collection table?
          odie_63

          How can I create an index on these ordered collection tables in order to improve performance?

          Which column(s) do you want to index ?

          • 2. Re: How to create index on XMLTYPE ordered collection table?
            978225

            Whatever I need to do to improve the performance of the above mentioned SELECT.

             

            The data is in a object-relationally stored XMLTYPE column (named XML_CONTENT). I assume the cost goes into the 1:n relation from EVENT to ACTION inside the XML document. ACTION is internally stored in the ordered collection table T_OR_MON_ACTION

            So whatever I need to do - the problem is I am not sure, that's why I can not get the syntax right, I guess...

             

            Then I need to do the same for the 1:n from ACTION to PARAM, but I guess I can figure that one out myself once the first one works...

            • 3. Re: How to create index on XMLTYPE ordered collection table?
              978225

              What irritates me too is this triple type cast just to find out whether the actionSubId is NULL:

                   filter(CAST(TO_NUMBER(TO_CHAR("SYS_ALIAS_0"."actionSubId")) AS INTEGER ) IS NOT NULL)


              Why is that? How can I display the type of those internal columns?

              • 4. Re: How to create index on XMLTYPE ordered collection table?
                odie_63

                If the schema is not annotated then xs:integer and xs:string are mapped to NUMBER and VARCHAR2(4000) datatypes, so you have to use those in your query in order to avoid unnecessary typecasting operations.

                 

                You should also use chained XMLTABLEs when accessing a parent/child relationship instead of a FLWOR expression, otherwise the CBO may not rewrite the XQuery correctly (maybe it's fixed in the latest release).

                 

                If you make those changes, the plan should show cleaner predicates :

                SQL> SELECT EVENT_ID, ACTION_SUB_ID, MESSAGE_ID, ACTION_TYPE, ACTION_TIMESTAMP

                  2  FROM test_table,

                  3  XMLTABLE('/monitoring'

                  4    PASSING XML_CONTENT COLUMNS

                  5      EVENT_ID         VARCHAR2(4000) PATH 'eventId',

                  6      actions          XMLTYPE        PATH 'action'

                  7    ) T1,

                  8  XMLTABLE('/action'

                  9    PASSING actions COLUMNS

                10      ACTION_SUB_ID    NUMBER PATH 'actionSubId',

                11      ACTION_TYPE      VARCHAR2(4000) PATH 'type',

                12      ACTION_TIMESTAMP NUMBER PATH 'time'

                13  ) T2

                14  WHERE EVENT_ID IS NOT NULL

                15  AND ACTION_SUB_ID IS NOT NULL

                16  ;

                 

                Execution Plan

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

                Plan hash value: 1763884463

                 

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

                | Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |

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

                |   0 | SELECT STATEMENT             |                 |   109 |   220K|     6  (17)| 00:00:01 |

                |   1 |  MERGE JOIN                  |                 |   109 |   220K|     6  (17)| 00:00:01 |

                |*  2 |   TABLE ACCESS BY INDEX ROWID| TEST_TABLE      |    11 |   352 |     2   (0)| 00:00:01 |

                |   3 |    INDEX FULL SCAN           | SYS_C007567     |    11 |       |     1   (0)| 00:00:01 |

                |*  4 |   SORT JOIN                  |                 |   109 |   216K|     4  (25)| 00:00:01 |

                |*  5 |    TABLE ACCESS FULL         | T_OR_MON_ACTION |   109 |   216K|     3   (0)| 00:00:01 |

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

                 

                Predicate Information (identified by operation id):

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

                 

                   2 - filter("TEST_TABLE"."SYS_NC00012$" IS NOT NULL)

                   4 - access("SYS_ALIAS_0"."NESTED_TABLE_ID"="TEST_TABLE"."SYS_NC0000800009$")

                       filter("SYS_ALIAS_0"."NESTED_TABLE_ID"="TEST_TABLE"."SYS_NC0000800009$")

                   5 - filter("SYS_ALIAS_0"."actionSubId" IS NOT NULL)

                 

                Note

                -----

                   - dynamic sampling used for this statement (level=2)

                 

                 

                Now, if still necessary, it all boils down to choosing a technique to index NULL values :

                - composite index with a not null or constant column

                - FBI

                - bitmap

                 

                Pick one that best fits your data, selectivity and activity on the tables.

                • 5. Re: How to create index on XMLTYPE ordered collection table?
                  978225

                  Thank you for the reply, I'll try that.

                   

                  I am having a hard time finding documentation about object-relationally stored XML. Is there any documentation other than the online Oracle documentation? That one is pretty thin in that respect as well...

                  Google also hardly brings up anything...

                  • 6. Re: How to create index on XMLTYPE ordered collection table?
                    odie_63

                    Is there any documentation other than the online Oracle documentation? That one is pretty thin in that respect as well...

                    Thin?

                    Are you sure you've read it entirely? I guess not.

                    In particular, it explains the mapping between XML and SQL datatypes, how to annotate a schema, how to create indexes on nested tables, in brief all you need to solve the very issues raised in your post

                     

                    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/partpg2.htm#g997354

                     

                    In addition, I encourage you to read the technical white papers here :

                    http://www.oracle.com/technetwork/database-features/xmldb/overview/index.html

                    • 7. Re: How to create index on XMLTYPE ordered collection table?
                      978225

                      Admittedly, I have read many passages but not the whole thing front to back. I tried searching for object-relational storage and how to index that, but everything pointed to the one example I had referenced in my original post. Everything else I found was indexing using XmlIndex on LOB storage XMLType.

                       

                      I could not get the index based on the example to work for the longest time because of a syntactical detail: the DB kept telling me that it did not know my field names until I put double-quotes around them. It wouldn't take  actionSubId , I  had to make it "actionSubId" in quotes.

                       

                      Anyway, thank you for the pointer to the white paper, I will check that out.

                       

                      The nested table syntax was the breakthrough: so far, I had repeatedly cancelled the loop-based execution after it had run for a day and V$SESSION_LONGOPS claimed several days of remaining runtime. With the nested syntax, everything was done after less than one hour. Although, in between, V$SESSION_LONGOPS had an estimated remaining runtime of 777,000 (!!!) seconds on some kind of table scan. But suddenly that had gone away and the query was done...

                      • 8. Re: How to create index on XMLTYPE ordered collection table?
                        978225

                        I have to come back to this question once more...

                         

                        • I did change around the data types in the mapping to get rid of the casts.
                        • I did figure out the syntax problem I had with creating the indexes on the collection tables
                        • I did rearrange the query to nested XMLTABLEs.

                        As  wrote previously, the last point gave a huge performance boost. However, the explain plan shows that none of my indexes are used.

                        I have a feeling (and I may be wrong??), that I should be able to get even better performance by creating the right indexes. I can not figure out what those are, though...

                         

                        Here is the latest explain plan:

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

                        | Id  | Operation                | Name                         | Rows  | Bytes|TempSpc| Cost (%CPU)| Time     |

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

                        |   0 | INSERT STATEMENT         |                              |    40M|    91G|       |  4837K  (1)| 16:07:31 |

                        |   1 |  LOAD AS SELECT          | T_PC_RMP_MONITORING_PARAM_IL |       |       |       |            |          |

                        |*  2 |   HASH JOIN              |                              |    40M|    91G|  6084M|  4837K  (1)| 16:07:31 |

                        |*  3 |    HASH JOIN             |                              |    25M|  5793M|   104M|   156K  (1)| 00:31:18 |

                        |*  4 |     HASH JOIN RIGHT SEMI |                              |   531K|    98M|  9480K| 10298   (1)| 00:02:04 |

                        |   5 |      INDEX FAST FULL SCAN| I_PC_RMP_MNTRNG_EVT_IL_MSGID |   538K|  3157K|       |   315   (1)| 00:00:04 |

                        |*  6 |      TABLE ACCESS FULL   | T_C_RMP_MNTRNG_XML_FULL_IL   |   539K|    96M|       |  4411   (1)| 00:00:53 |

                        |*  7 |     TABLE ACCESS FULL    | T_OR_MON_ACTION              |    25M|  1107M|       | 71254   (1)| 00:14:16 |

                        |*  8 |    TABLE ACCESS FULL     | T_OR_MON_ACTION_PARAM        |    40M|    84G|       | 80198   (1)| 00:16:03 |

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

                        Predicate Information (identified by operation id):

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

                           2 - access("NESTED_TABLE_ID"="SYS_ALIAS_0"."SYS_NC0000500006$")

                           3 - access("SYS_ALIAS_0"."NESTED_TABLE_ID"="T_C_RMP_MNTRNG_XML_FULL_IL"."SYS_NC0001200013$")

                           4 - access("MESSAGE_ID"="MESSAGE_ID")

                           6 - filter("T_C_RMP_MNTRNG_XML_FULL_IL"."SYS_NC00016$" IS NOT NULL)

                           7 - filter("SYS_ALIAS_0"."ACTION_SUB_ID" IS NOT NULL)

                           8 - filter("SYS_ALIAS_1"."KEY" IS NOT NULL)

                        Note

                           - dynamic sampling used for this statement (level=2)

                         

                        Do you have any pointers for an index that the optimizer should be picking up? You had some hints for the NOT NULL checks earlier, but I am thinking about indexes to optimize the joins between the OCTs. Or should I not even worry about those?

                         

                        Thank you...

                        • 9. Re: How to create index on XMLTYPE ordered collection table?
                          Marco Gralike

                          > it wouldn't take  actionSubId , I  had to make it "actionSubId" in quotes.

                           

                          Everything in XMLDB land is case-sensitive...

                           

                          > The nested table syntax was the breakthrough:

                           

                          That's why Oracle made this default while generating objects via getables / DBMS_XMLSCHEMA (I think from 11.2 and onwards)