Question: I don't understand the explain plan step "collection
iterator pickler fetch. I am using this query:
SELECT p.order_number, p.shipping_date, p.city,
o.line_number, o.quantity, o.products.product_number, o.products.description
FROM purchaseorder_t p, TABLE(p.orderlineitem) o;
I generated the explain plan command for the above query and found that Oracle using
the COLLECTION ITERATOR (PICKLER FETCH).
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=220564 Card=81680000
Bytes=4900800000)
1 0 NESTED LOOPS (Cost=220564 Card=81680000 Bytes=4900800000)
2 1 TABLE ACCESS (FULL) OF 'PURCHASEORDER_T' (TABLE) (Cost=180 Card=10000
Bytes=580000)
3 1 COLLECTION ITERATOR (PICKLER FETCH)
Can anyone tell me what is COLLECTION ITERATOR PICKLER FETCH?
Answer: In general, a pickler fetch is the process of converting
a packed array into a displayable format. For more details on Oracle
object-oriented database, see my notes on
Oracle
objects.
Oracle consultant
Steve Adams notes that the collection iterator with a pickler fetch is used
to un-pack object type table columns:
"Pickling is serializing arbitrary object-oriented data structures.
That is, converting them into a byte stream for storage, transmission
over a network or iterative navigation as in this case. It is a more complex
process than you might imagine. If you want more information, search the web
for information on Python's pickle module.
I guess this is the way Oracle dereferences the embedded collections?"
The execution plan step "COLLECTION ITERATOR (PICKLER FETCH)" is the
optimizer plan operation showing use of the dbms_pickler package, a
PL/SQL package which is used to get TDS (Type Descriptor Source) of a object.
This is required to know the contents of the given ADT (ie., attributes in a
Abstract Data Type). During a pickler fetch, the dbms_pickler
package is called to convert the array data type to a byte stream that can be
displayed.
Starting in Oracle8i (8.1.5), the pickler fetch and collection
iterator operations were introduced, and new CBO access methods that were added
to Oracle SQL to manage "objects" (0NF tables, e.g. nested tables, varray
tables, tables with abstract data type columns).
The pickler code is very new in Oracle8i and there are several reported
memory leak issues with pickler fetches, even in Oracle 11g. As for performance,
the collection iterator operation is used to extract the embedded repeating
group from the row.
Remember, rows with varray items are stored
in-line so that a fetch for the data block that contains the row will also
contain the repeating groups. Hence, the performance of tables with varray
columns is comparable to standard row select statements.
OPERATION
--------------------------------------------------------------------------------
OPTIONS OBJECT_NAME POSITION
------------------------------ ------------------------------ ----------
SELECT STATEMENT
33
NESTED LOOPS
1
TABLE ACCESS
FULL EMP 1
and include the URL for the page.
