Home > XML DB, XQuery > New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause

New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause

September 9, 2013 Leave a comment Go to comments

This is a short article about the new 12c RETURNING SEQUENCE BY REF feature available with XMLTable function.
This clause allows us to access the complete tree of nodes produced by the main XQuery expression. In particular, we can now use reverse axes in the COLUMNS PATH expression.
Below is an example using this method, compared with prior releases’ techniques to achieve the same result (all queries tested on 12.1.0.1).

 

1. Set up

This script creates an XMLType table (binary XML storage) and a sample XML document :

create table tmp_xml of xmltype;

insert into tmp_xml
select xmlelement("items"
       , xmlagg(
           xmlelement("item"
           , xmlattributes(
               p.id as "id"
             , 'item' || to_char(p.id, 'fm0999') as "name"
             )
           , (
               select xmlelement("subitems"
                      , xmlagg(
                          xmlelement("subitem"
                          , xmlforest(
                              p.id || '.' || level as "id"
                            , 'subitem' || to_char(level, 'fm09') as "name"
                            )
                          )
                        )
                      )
               from dual
               connect by level <= 10
             )
           )
         )
       )
from (
  select level as id
  from dual
  connect by level < 10000
) p ;

The XML document has the following structure :

<items>
  <item id="1" name="item0001">
    <subitems>
      <subitem>
        <id>1.1</id>
        <name>subitem01</name>
      </subitem>
      <subitem>
        <id>1.2</id>
        <name>subitem02</name>
      </subitem>
      <subitem>
        <id>1.3</id>
        <name>subitem03</name>
      </subitem>
      <subitem>
        <id>1.4</id>
        <name>subitem04</name>
      </subitem>
      <subitem>
        <id>1.5</id>
        <name>subitem05</name>
      </subitem>
      <subitem>
        <id>1.6</id>
        <name>subitem06</name>
      </subitem>
      <subitem>
        <id>1.7</id>
        <name>subitem07</name>
      </subitem>
      <subitem>
        <id>1.8</id>
        <name>subitem08</name>
      </subitem>
      <subitem>
        <id>1.9</id>
        <name>subitem09</name>
      </subitem>
      <subitem>
        <id>1.10</id>
        <name>subitem10</name>
      </subitem>
    </subitems>
  </item>

  <!-- 9997 more item nodes -->

  <item id="9999" name="item9999">
    <subitems>
      <!-- 10 subitem nodes -->
    </subitems>
  </item>
</items>

and we want to flatten it, displaying each subitem in one row along with its parent (item node) information, like this :

 ITEM_ID ITEM_NAME  SUB_ID               SUB_NAME
-------- ---------- -------------------- ----------
       1 item0001   1.1                  subitem01
       1 item0001   1.2                  subitem02
       1 item0001   1.3                  subitem03
       1 item0001   1.4                  subitem04
       1 item0001   1.5                  subitem05
       1 item0001   1.6                  subitem06
       1 item0001   1.7                  subitem07
       1 item0001   1.8                  subitem08
       1 item0001   1.9                  subitem09
       1 item0001   1.10                 subitem10
       2 item0002   2.1                  subitem01
       2 item0002   2.2                  subitem02
...

 

2. The new 12c method

SQL> select x.*
  2  from tmp_xml t
  3     , xmltable(
  4         '/items/item/subitems/subitem'
  5         passing t.object_value
  6         returning sequence by ref
  7         columns item_id   integer      path '../../@id'
  8               , item_name varchar2(10) path '../../@name'
  9               , sub_id    varchar2(20) path 'id'
 10               , sub_name  varchar2(10) path 'name'
 11       ) x
 12  ;

99990 rows selected.

Elapsed: 00:00:08.14

Execution Plan
----------------------------------------------------------
Plan hash value: 1416101436

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  8168 |   757K|    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |         |  8168 |   757K|    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | TMP_XML |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   XMLTABLE EVALUATION |         |       |       |            |          |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
        348  physical reads
          0  redo size
    3602313  bytes sent via SQL*Net to client
      73858  bytes received via SQL*Net from client
       6667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99990  rows processed

 

3. Chained XMLTABLEs with forward axes

This is the most common and performant way to achieve our requirement in prior releases. We’ll see how it compares to the new approach in the conclusion.

SQL> select x1.item_id, x1.item_name, x2.*
  2  from tmp_xml t
  3     , xmltable(
  4         '/items/item'
  5         passing t.object_value
  6         columns item_id   integer      path '@id'
  7               , item_name varchar2(10) path '@name'
  8               , subitems  xmltype      path 'subitems'
  9       ) x1
 10     , xmltable(
 11         '/subitems/subitem'
 12         passing x1.subitems
 13         columns sub_id   varchar2(20) path 'id'
 14               , sub_name varchar2(10) path 'name'
 15       ) x2
 16  ;

99990 rows selected.

Elapsed: 00:00:23.08

Execution Plan
----------------------------------------------------------
Plan hash value: 2982218919

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    66M|  6171M|   221K  (1)| 00:00:09 |
|   1 |  NESTED LOOPS       |         |    66M|  6171M|   221K  (1)| 00:00:09 |
|   2 |   NESTED LOOPS      |         |  8168 |   741K|    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TMP_XML |     1 |    87 |     3   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |         |       |       |            |          |
|   5 |   XPATH EVALUATION  |         |       |       |            |          |
-------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
      10627  consistent gets
      31416  physical reads
          0  redo size
    3602313  bytes sent via SQL*Net to client
      73858  bytes received via SQL*Net from client
       6667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99990  rows processed

 

4. Single XMLTABLE with forward axes

SQL> select x.*
  2  from tmp_xml t
  3     , xmltable(
  4         'for $i in /items/item
  5            , $j in $i/subitems/subitem
  6          return element r {
  7            element item_id   {data($i/@id)}
  8          , element item_name {data($i/@name)}
  9          , element sub_id    {$j/id/text()}
 10          , element sub_name  {$j/name/text()}
 11          }'
 12         passing t.object_value
 13         columns item_id   integer      path 'item_id'
 14               , item_name varchar2(10) path 'item_name'
 15               , sub_id    varchar2(20) path 'sub_id'
 16               , sub_name  varchar2(10) path 'sub_name'
 17       ) x
 18  ;

99990 rows selected.

Elapsed: 00:10:36.82

Execution Plan
----------------------------------------------------------
Plan hash value: 2982218919

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |    66M|  6171M|   221K  (1)| 00:00:09 |
|   1 |  NESTED LOOPS       |         |    66M|  6171M|   221K  (1)| 00:00:09 |
|   2 |   NESTED LOOPS      |         |  8168 |   741K|    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| TMP_XML |     1 |    87 |     3   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |         |       |       |            |          |
|   5 |   XPATH EVALUATION  |         |       |       |            |          |
-------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
     209993  consistent gets
     330581  physical reads
          0  redo size
    3602313  bytes sent via SQL*Net to client
      73858  bytes received via SQL*Net from client
       6667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99990  rows processed

That one shows lots of IO operations. The bad performance observed is confirmed by the warning “Unoptimized XML construct detected” while displaying the explain plan.
Rather counterintuitively, it gets way more performant if we use the NO_XML_QUERY_REWRITE hint. Also note the change in the plan from a streaming XPath evaluation to the new XMLTABLE EVALUATION operation :

SQL> select /*+ no_xml_query_rewrite */ x.*
  2  from tmp_xml t
  3     , xmltable(
  4         'for $i in /items/item
  5            , $j in $i/subitems/subitem
  6          return element r {
  7            element item_id   {data($i/@id)}
  8          , element item_name {data($i/@name)}
  9          , element sub_id    {$j/id/text()}
 10          , element sub_name  {$j/name/text()}
 11          }'
 12         passing t.object_value
 13         columns item_id   integer      path 'item_id'
 14               , item_name varchar2(10) path 'item_name'
 15               , sub_id    varchar2(20) path 'sub_id'
 16               , sub_name  varchar2(10) path 'sub_name'
 17       ) x
 18  ;

99990 rows selected.

Elapsed: 00:00:16.74

Execution Plan
----------------------------------------------------------
Plan hash value: 1416101436

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  8168 |   757K|    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |         |  8168 |   757K|    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | TMP_XML |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   XMLTABLE EVALUATION |         |       |       |            |          |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        196  consistent gets
        348  physical reads
          0  redo size
    3602313  bytes sent via SQL*Net to client
      73858  bytes received via SQL*Net from client
       6667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99990  rows processed

 

5. Single XMLTABLE with reverse axes

SQL> select x.*
  2  from tmp_xml t
  3     , xmltable(
  4         'for $i in /items/item/subitems/subitem
  5          return element r {
  6            element item_id   {data($i/../../@id)}
  7          , element item_name {data($i/../../@name)}
  8          , $i/id
  9          , $i/name
 10          }'
 11         passing t.object_value
 12         columns item_id   integer      path 'item_id'
 13               , item_name varchar2(10) path 'item_name'
 14               , sub_id    varchar2(20) path 'id'
 15               , sub_name  varchar2(10) path 'name'
 16       ) x
 17  ;

99990 rows selected.

Elapsed: 00:10:37.25

Execution Plan
----------------------------------------------------------
Plan hash value: 1416101436

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |  8168 |   757K|    32   (0)| 00:00:01 |
|   1 |  NESTED LOOPS         |         |  8168 |   757K|    32   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | TMP_XML |     1 |    87 |     3   (0)| 00:00:01 |
|   3 |   XMLTABLE EVALUATION |         |       |       |            |          |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
        196  consistent gets
        348  physical reads
          0  redo size
    3602313  bytes sent via SQL*Net to client
      73858  bytes received via SQL*Net from client
       6667  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      99990  rows processed

 

6. Conclusion

Method Rows fetched Overall time (second)
New 12c RETURNING SEQUENCE BY REF 99990 8
Single XMLTABLE with forward axes + funct. eval. 99990 17
Chained XMLTABLEs with forward axes 99990 23
Single XMLTABLE with reverse axes 99990 637

For this specific case, the new feature clearly outperforms earlier methods.
Besides, we can see that the new XMLTABLE EVALUATION operation also opens interesting perspectives for XQuery processing.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s