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:

I hope I have been of help.

;-)

m4s0n501
Written by:

2 Comments

  1. Srini
    3/13/2011

    Hi,

    Wondering why there are no comments. This is of great use..thanks for this.

    This is good for starters and for somebody who are looking into some complex XMLTable examples, I would like to direct them to this link.

  2. 3/13/2011

    My first steps ;-)

    Keep up the good work. For very very good examples regarding XQuery, XML/SQL, XPath solutions also follow the top submitters on the OTN XMLDB forum just under Mark and me.

Comments are closed.