1 2 Previous Next 20 Replies Latest reply: Nov 19, 2010 2:37 PM by user10945315 RSS

    how to create index on xmtype column

    user10945315
      Hi,

      I have a table as follows

      CREATE TABLE T_TEST_XML (PROCESS_ID NUMBER, TXT_XML XMLTYPE);


      I query the above table very frequently with the query

      SELECT * FROM T_TEST_XML TXS WHERE EXISTSNODE(TXS.TXT_XML, '/order[status="PEN"]') = 1


      How to create function based index on the TXT_XML column for the xpath expression /order[status="PEN"]' to improve the query performance?

      Thank you
        • 1. Re: how to create index on xmtype column
          mdrake
          Which DB Version

          In 11g and later forget functional indexes.

          Use

          create index xmlIndex on T_TEST_XML(TEXT_XML) indexType is xdb.xmlindex;

          Also use XMLExists instead of existsNode()

          Also consider using SECUREFILE Binary XML for storing the XML

          SQL> CREATE TABLE T_TEST_XML (PROCESS_ID NUMBER, TXT_XML XMLTYPE)
            2  XMLTYPE TXT_XML store as SECUREFILE BINARY XML
            3  ;
          
          Table created.
          
          SQL> create INDEX T_TEXT_XML_IDX on T_TEST_XML(TXT_XML)
            2  indexType is XDB.XMLINdex
            3  /
          
          Index created.
          
          SQL> set autotrace on explain
          SQL> --
          SQL> SELECT *
            2    FROM T_TEST_XML TXS
            3   WHERE XMLEXISTS('$xml/order[status="PEN"]' passing TXT_XML as "xml")
            4  /
          
          no rows selected
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2584143648
          
          ---------------------------------------------------------------------------------------------------------------
          | Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT             |                                |     1 |  5049 |     2   (0)| 00:00:01 |
          |   1 |  NESTED LOOPS SEMI           |                                |     1 |  5049 |     2   (0)| 00:00:01 |
          |   2 |   TABLE ACCESS FULL          | T_TEST_XML                     |     1 |  2027 |     2   (0)| 00:00:01 |
          |*  3 |   TABLE ACCESS BY INDEX ROWID| SYS1019787_T_TEXT_X_PATH_TABLE |     1 |  3022 |     0   (0)| 00:00:01 |
          |*  4 |    INDEX RANGE SCAN          | SYS1019787_T_TEXT_X_PIKEY_IX   |     1 |       |     0   (0)| 00:00:01 |
          ---------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             3 - filter("SYS_P0"."VALUE"='PEN' AND SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
             4 - access("TXS".ROWID="SYS_P0"."RID" AND "SYS_P0"."PATHID"=HEXTORAW('644F') )
          
          Note
          -----
             - dynamic sampling used for this statement (level=2)
          
          SQL>
          SQL>
          • 2. Re: how to create index on xmtype column
            mdrake
            Note the plan will improve once the table is populated and statistics are collected.
            • 3. Re: how to create index on xmtype column
              user10945315
              The database version is 10.2.0.1
              • 4. Re: how to create index on xmtype column
                odie_63
                See this, from the XML DB documentation :

                http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14259/xdb04cre.htm#sthref596
                • 5. Re: how to create index on xmtype column
                  user10945315
                  I have gone through the documentation link mentioned above, but i was unable to create the index. Can you let me know the syntax for creating function based index on the xmltype column
                  • 6. Re: how to create index on xmtype column
                    odie_63
                    CREATE INDEX my_index ON t_test_xml ( existsNode(txt_xml,'/order[status="PEN"]') );
                    • 7. Re: how to create index on xmtype column
                      mdrake
                      Actually if you are limited to using older software

                      1. Consider defining an XML Schema and storing the XML using object relational storage.

                      or

                      2. If you must store the XML as CLOB create the index on extractValue(), rather than existsNode() and supply the predicate value at the SQL level rather than the XPATH level.
                      SQL> DROP TABLE T_TEST_XML
                        2  /
                      
                      Table dropped.
                      
                      SQL> CREATE TABLE T_TEST_XML (PROCESS_ID NUMBER, TXT_XML XMLTYPE)
                        2  /
                      
                      Table created.
                      
                      SQL> create INDEX T_TEXT_XML_IDX on T_TEST_XML( extractValue(TXT_XML,'/order/status'))
                        2  /
                      
                      Index created.
                      
                      SQL> set autotrace on explain
                      SQL> --
                      SQL> SELECT *
                        2    FROM T_TEST_XML TXS
                        3   WHERE ExistsNode(TXT_XML,'/order[status="PEN"]') = 1
                        4  /
                      
                      no rows selected
                      
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 3001212210
                      
                      ---------------------------------------------------------------------------------
                      | Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                      ---------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT   |            |     1 |  2017 |    31   (0)| 00:00:01 |
                      |   1 |  NESTED LOOPS SEMI |            |     1 |  2017 |    31   (0)| 00:00:01 |
                      |   2 |   TABLE ACCESS FULL| T_TEST_XML |     1 |  2015 |     2   (0)| 00:00:01 |
                      |*  3 |   XPATH EVALUATION |            |       |       |            |          |
                      ---------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         3 - filter("P"."C_01$"='PEN')
                      
                      Note
                      -----
                         - dynamic sampling used for this statement (level=2)
                      
                      SQL> SELECT *
                        2    FROM T_TEST_XML TXS
                        3   WHERE extractValue(TXT_XML,'/order/status') = 'PEN'
                        4  /
                      
                      no rows selected
                      
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 1430727070
                      
                      ----------------------------------------------------------------------------------------------
                      | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                      ----------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT            |                |     1 |  2015 |     1   (0)| 00:00:01 |
                      |   1 |  TABLE ACCESS BY INDEX ROWID| T_TEST_XML     |     1 |  2015 |     1   (0)| 00:00:01 |
                      |*  2 |   INDEX RANGE SCAN          | T_TEXT_XML_IDX |     1 |       |     1   (0)| 00:00:01 |
                      ----------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - access(EXTRACTVALUE(SYS_MAKEXML(0,"SYS_NC00003$"),'/order/status',null,0,0,5242
                                    93,133120)='PEN')
                      
                      Note
                      -----
                         - dynamic sampling used for this statement (level=2)
                      
                      SQL>
                      SQL>
                      SQL>
                      SQL>
                      SQL>
                      SQL>
                      This allows the index to support querying on any value of status, rather than just PEN
                      • 8. Re: how to create index on xmtype column
                        user10945315
                        thank you, that helps
                        • 9. Re: how to create index on xmtype column
                          Marco Gralike
                          Mark, which database version did you use for this example ?
                          • 10. Re: how to create index on xmtype column
                            mdrake
                            11.2.0.2.0 ...
                            • 11. Re: how to create index on xmtype column
                              user10945315
                              Mark,

                              I followed the same steps for creating index.

                              When the table is loaded with data the query below is doing a full table scan

                              SQL> SELECT *
                              2 FROM T_TEST_XML TXS
                              3 WHERE extractValue(TXT_XML,'/order/status') = 'PEN'
                              4 /


                              The explain plan shows TABLE ACCESS BY INDEX ROWID| T_TEST_XML | 1 | 2015 | 1 (0)| 00:00:01 |
                              |* 2 | INDEX RANGE SCAN only when the table is empty.( when table has data , it is doing a full table scan)

                              I want to improve the performance of this query and make it run faster.Do you have any suggestions on how to do this?

                              Thanks
                              • 12. Re: how to create index on xmtype column
                                Marco Gralike
                                Did you create the statistics for the table/index ?

                                While using the this statement how selective is this '/order/status' regarding 'PEN' ??? That is how big is the percentage of xml documents of the total that have a /order/status='PEN' condition?

                                If Oracle has to get to much data then sometimes it more efficient to do a full table scan because the total I/O done for a full table scan can be way less expensive than the total I/O for reading the whole index (leaves/branches) AND the total I/O for all the rows fetched from the table. If the optimizer has all the needed info, stats, it might consider that a full table scan therefore is less costly than the fetching and index and table data. Btw you asked in this statement to get all the XML content where */order/status = 'PEN'*. You didn't ask the database to be a bit more selective by asking for example "give me all the status values or value X or /order/orderids where /order/status='PEN'...

                                Edited by: Marco Gralike on Nov 19, 2010 8:41 PM
                                • 13. Re: how to create index on xmtype column
                                  user10945315
                                  Yes, i created stats. There are a total of 25400 rows in the table. And out of them 11811 are in 'PEN' status

                                  Thanks
                                  • 14. Re: how to create index on xmtype column
                                    mdrake
                                    The problem here is that we do not know the 'cost' of performing the extractValue().. So the index tells us that since 50% of the rows match it's cheaper to do tablescan. The real answer here is to get to 11.2 where we can use binary XML and XMLIndex to solve this issue...
                                    1 2 Previous Next