1 Reply Latest reply: Sep 24, 2012 8:33 AM by odie_63 RSS

    With Oracle XML Tables do XQuery selects use XmlIndexes?

    963962
      I am trying to retrieve keys and parent keys from some structured xml stored as binary xml in oracle. I have tried created unstructured index and also an index with a structured component. The structured component works fine when doing a SELECT against XMLTABLE() but I cannot retrieve values of parent node using XMLTable. I am therefore trying the following Xquery to retrieve parent values but this is not using the index at all. Does this style of query support using XmlIndexes? I can't find anything in the docs that say either way.

      SELECT y.*
      FROM xml_data x, XMLTABLE(xmlnamespaces( DEFAULT 'namespace'),
      'for $i in /foo/bar
      return element r {
      $i/someKey
      ,element parentKey { $i/../someKey }
      }'
      PASSING x.import_xml
      COLUMNS
      someKey VARCHAR2(100) PATH 'someKey'
      ,parentKey VARCHAR2(100) PATH 'parentKey'
      ) y

      thanks, Tom
        • 1. Re: With Oracle XML Tables do XQuery selects use XmlIndexes?
          odie_63
          Does this style of query support using XmlIndexes? I can't find anything in the docs that say either way.
          No, parent axes are not indexed :
          http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_indexing.htm#autoId21

          However you can rewrite the query so that it uses only forward axes instead.
          The unstructured index will be used :
          SQL> create table tmp_xml of xmltype;
          
          Table created.
          
          SQL> create index tmp_xml_uix on tmp_xml (object_value) indextype is xdb.xmlindex;
          
          Index created.
          
          SQL> insert into tmp_xml values (
            2  xmlparse(document '<foo someKey="1" xmlns="my-namespace-uri">
            3   <bar someKey="1.1"></bar>
            4   <bar someKey="1.2"></bar>
            5  </foo>')
            6  );
          
          1 row created.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> call dbms_stats.gather_table_stats(user, 'TMP_XML');
          
          Call completed.
          
          SQL> set autotrace on
          SQL> set lines 200
          SQL> SELECT x.*
            2  FROM tmp_xml t
            3     , XMLTable(
            4         XMLNamespaces(default 'my-namespace-uri')
            5       , 'for $i in /foo
            6            , $j in $i/bar
            7          return element r {
            8            element someKey   { data($j/@someKey) }
            9          , element parentKey { data($i/@someKey) }
           10          }'
           11         passing t.object_value
           12         columns
           13           parentKey VARCHAR2(15) PATH 'parentKey'
           14         , someKey   VARCHAR2(15) PATH 'someKey'
           15       ) x
           16  ;
          
          PARENTKEY       SOMEKEY
          --------------- ---------------
          1               1.1
          1               1.2
          
          
          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 942493706
          
          ----------------------------------------------------------------------------------------------------------------
          | Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
          ----------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT              |                                |     1 |    80 |     5   (0)| 00:00:01 |
          |*  1 |  FILTER                       |                                |       |       |            |          |
          |*  2 |   TABLE ACCESS BY INDEX ROWID | SYS75010_TMP_XML_UI_PATH_TABLE |     1 |    39 |     2   (0)| 00:00:01 |
          |*  3 |    INDEX RANGE SCAN           | SYS75010_TMP_XML_UI_PIKEY_IX   |     1 |       |     1   (0)| 00:00:01 |
          |*  4 |  FILTER                       |                                |       |       |            |          |
          |*  5 |   TABLE ACCESS BY INDEX ROWID | SYS75010_TMP_XML_UI_PATH_TABLE |     1 |    39 |     2   (0)| 00:00:01 |
          |*  6 |    INDEX RANGE SCAN           | SYS75010_TMP_XML_UI_PIKEY_IX   |     1 |       |     1   (0)| 00:00:01 |
          |   7 |  NESTED LOOPS                 |                                |     1 |    80 |     5   (0)| 00:00:01 |
          |   8 |   NESTED LOOPS                |                                |     1 |    68 |     4   (0)| 00:00:01 |
          |*  9 |    TABLE ACCESS BY INDEX ROWID| SYS75010_TMP_XML_UI_PATH_TABLE |     1 |    34 |     2   (0)| 00:00:01 |
          |* 10 |     INDEX SKIP SCAN           | SYS75010_TMP_XML_UI_PIKEY_IX   |     1 |       |     1   (0)| 00:00:01 |
          |* 11 |    TABLE ACCESS BY INDEX ROWID| SYS75010_TMP_XML_UI_PATH_TABLE |     1 |    34 |     2   (0)| 00:00:01 |
          |* 12 |     INDEX RANGE SCAN          | SYS75010_TMP_XML_UI_PIKEY_IX   |     1 |       |     1   (0)| 00:00:01 |
          |  13 |   TABLE ACCESS BY USER ROWID  | TMP_XML                        |     1 |    12 |     1   (0)| 00:00:01 |
          ----------------------------------------------------------------------------------------------------------------
          
          Predicate Information (identified by operation id):
          ---------------------------------------------------
          
             1 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
             2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P8"."LOCATOR")=1)
             3 - access("SYS_P8"."RID"=:B1 AND "SYS_P8"."PATHID"=HEXTORAW('3D8D')  AND "SYS_P8"."ORDER_KEY">:B2
                        AND "SYS_P8"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3))
                 filter(SYS_ORDERKEY_DEPTH("SYS_P8"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B1)+1)
             4 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
             5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1)
             6 - access("SYS_P5"."RID"=:B1 AND "SYS_P5"."PATHID"=HEXTORAW('5A93')  AND "SYS_P5"."ORDER_KEY">:B2
                        AND "SYS_P5"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3))
                 filter(SYS_ORDERKEY_DEPTH("SYS_P5"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B1)+1)
             9 - filter(SYS_XMLI_LOC_ISNODE("SYS_ALIAS_4"."LOCATOR")=1)
            10 - access("SYS_ALIAS_4"."PATHID"=HEXTORAW('7040') )
                 filter("SYS_ALIAS_4"."PATHID"=HEXTORAW('7040') )
            11 - filter(SYS_XMLI_LOC_ISNODE("SYS_ALIAS_2"."LOCATOR")=1)
            12 - access("SYS_ALIAS_4"."RID"="SYS_ALIAS_2"."RID" AND "SYS_ALIAS_2"."PATHID"=HEXTORAW('61C9')  AND
                        "SYS_ALIAS_2"."ORDER_KEY"<"SYS_ALIAS_4"."ORDER_KEY")
                 filter("SYS_ALIAS_4"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD("SYS_ALIAS_2"."ORDER_KEY") AND
                        SYS_ORDERKEY_DEPTH("SYS_ALIAS_2"."ORDER_KEY")+1=SYS_ORDERKEY_DEPTH("SYS_ALIAS_4"."ORDER_KEY"))
          
          
          Statistics
          ----------------------------------------------------------
                   55  recursive calls
                    0  db block gets
                  716  consistent gets
                    0  physical reads
                    0  redo size
                  528  bytes sent via SQL*Net to client
                  419  bytes received via SQL*Net from client
                    2  SQL*Net roundtrips to/from client
                    0  sorts (memory)
                    0  sorts (disk)
                    2  rows processed