About Table(XMLSequence()) and XMLTable

A customer asked me how this XMLTable XML function works… Until now they had to use the TABLE(XMLSEQUENCE()) constructor.

While testing performance differences between a RedHat AS V3 (Oracle Enterprise Version 10.1.0.3, RAID 1+0) machine and a full blown LPAR IBM AIX 64b 5.3 environment (Oracle Enterprise Version 10.2.0.2) with EMC (RAID 5) they wanted to use the XMLTABLE XML function.

Why? Oracle promotes the use of the XMLTable function on 10.2.x and higher Oracle versions. My belief is that the TABLE(XMLSEQUENCE()) constructor will be deprecated in favor of the XMLTABLE function. Also the XMLTABLE function supports XQuery functionality.

They had a simple statement to test with.

SQL> set autotrace on

SQL> SELECT count(*) "AMOUNT"
  2  FROM   3020_JWB_FA t
  3  ,      TABLE(XMLSequence(extract(t.gegevens,'/MYAPP-JWB/PERSOON'
  4                                             , 'xmlns="http://www.mysite.nl/xsd/app/JWB/MYAPP-JWB-HISTORIE"'))) v
  5  ;

AMOUNT
------------------
                10

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 245705617

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |  2002 |   276   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE                     |                        |     1 |  2002 |            |          |
|   2 |   NESTED LOOPS                      |                        | 81680 |   155M|   276   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL                | 3020_JWB_FA            |    10 | 20020 |     3   (0)| 00:00:01 |
|   4 |    COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        445  recursive calls
          0  db block gets
        665  consistent gets
        296  physical reads
       1760  redo size
        421  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

So how to re-write this to the XMLTable XML function?

It is not so difficult, it will look like follows.

SQL> set autotrace on

SQL> SELECT count(*) as "AMOUNT"
  2  FROM   3020_JWB_FA 
  3  ,      XMLTABLE(xmlnamespaces(default 'http://www.mysite.nl/xsd/app/JWB/MYAPP-JWB-HISTORIE'),
  4                  '/MYAPP-JWB/PERSOON'
  5                  passing 3020_JWB_FA.gegevens
  6                 );

AMOUNT
------------------
                10

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 245705617

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                        |     1 |  2002 |   276   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE                     |                        |     1 |  2002 |            |          |
|   2 |   NESTED LOOPS                      |                        | 81680 |   155M|   276   (1)| 00:00:04 |
|   3 |    TABLE ACCESS FULL                | 3020_JWB_FA            |    10 | 20020 |     3   (0)| 00:00:01 |
|   4 |    COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE |       |       |            |          |
--------------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
       1457  recursive calls
          0  db block gets
        871  consistent gets
        298  physical reads
       1872  redo size
        421  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         37  sorts (memory)
          0  sorts (disk)
          1  rows processed

Be aware off…

Some of the items you should be aware of reading and maybe using these simple examples.

• First, both statements will NOT perform. Both statements can not use a index and will endure a “COLLECTION ITERATOR PICKLER FETCH” method. As you can see, the results are dramatic for a column with only 10 records in it. Always try to use and index (of course…if and only if, if it is appropriate).
• A “COLLECTION ITERATOR PICKLER FETCH” is the first sign you should be aware off that these statements will not perform. Fragments and/or XML Instance documents will be rebuild in memory during this procedure, while DOM validation will take place. This is dramatic for memory and CPU usage (most of the time you will get memory problems).
• Have you seen the XML Namespace reference? This is a much asked question: “Where and how do I add a XML Namespace reference in my statement“. A shortcut of the how and why, can be found on the Oracle XMLDB FAQ Forum site.

More information

You can find more information about the mentioned XML functions here:

• Mastering XML DB – COUNT and SUM
• XMLDB Howto’s on this site
• XMLDB Developers Guide for Oracle Database Version 10.2
• SQL Reference Guide for Oracle Database Version 10.2:
1. XMLTABLE
2. XMLSEQUENCE
3. EXTRACT