I found a very cool presentation. Yep it is about XML, The concepts behind XQuery…
Category: SQL, XML/SQL, XPath, XQuery
Driven by a post from Lewis about “OSDM: Rerverse Engineer A Schema“, I dared my luck to play with Oracle SQL Developer Data Modeler (OSDM), trying to see if it understands the XML database realm off doing things. It doesn’t understands it…at least, yet. I used the early adopter release 1.5.1 (build 518).
I wonder if it is reasonable that I am disappointed; In the long run the “XDB” schema that I used to reverse engineer isn’t relational but (at least) object-relational. Most of the object relational issues, OSDM understands, but not the theory behind XMLType tables. I tried it multiple times, in the end, even the “secondary tables” and “spatial properties” option you can check during the reverse enginering option.
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.