Tag: XQSEQUENCEFROMXMLTYPE

October 16

Nowadays, this “Collection Iterator Pickler Fetch” output in an explain plan is less and less seen as the XML database functionality becomes more and more stronger in re-writing statements. Alas, I got one, again.

SQL> xquery
  2   let $auction := ora:VIEW("XM_TAB") RETURN
  3   COUNT(
  4     FOR $i IN $auction/site/closed_auctions/closed_auction
  5     WHERE $i/price/text() >= 40
  6     RETURN $i/price
  7*  )
 
RESULT SEQUENCE
---------------
6539
 
Execution Plan
----------------------------------------------------------
Plan hash VALUE: 4083502163
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                  | ROWS  | Bytes | Cost (%CPU)| TIME     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                       |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |       |       |            |          |
-----------------------------------------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
        143  recursive calls
          0  db block gets
     385537  consistent gets
        119  physical reads
          0  redo SIZE
       1439  bytes sent via SQL*Net TO client
        942  bytes received via SQL*Net FROM client
          6  SQL*Net roundtrips TO/FROM client
      19500  sorts (memory)
          0  sorts (disk)
          1  ROWS processed
SQL>

I couldn’t find much on the internet about the “collection iterator pickler fetch” (lets call it CIPF for now). These posts from Steve Adams and Tom Kyte shed some light on what is happening.

In the XMLDB realm, I now found two examples of CIPF.