Hi,
I've a question about the correct sytax of collection()
According to the documentation the correct syntax should be:
collection("oradb:<nameSchema>:<nameTable>")/ROW/<nameColumn>
but another possible syntax is:
collection("oradb:<nameSchema>:<nameTable>/ROW/<nameColumn>")
in the second version the path "/ROW/<nameColumn>" is put inside the function collection.
I've tried both versions (I add at the mail the queries) and the result is always right,
but the performance are very different with the first query (that is recommended in documentation) much slower than the second.
After I checked the execution plans of both queries I noticed that the difference is in the last step: collection iterator vs xpath evaluation.
What is the correct syntax of function collection()?
Why are the plans different?
I'm using the version 11.2.0.3
Thanks
Michele Bassanelli
DECLARE
BEGIN
execute immediate 'CREATE TABLE TEST ( ID NUMBER(8,0), VALUE XMLTYPE )';
FOR i IN 1..10000 LOOP
insert into TEST values (i,'<root><value>'||i||'</value></root>');
END LOOP;
commit;
END;
Query 1:
SELECT xmlserialize(content COLUMN_VALUE as clob no indent) FROM XMLTABLE( '
count(collection("oradb:/TESTNG/TEST")/ROW/VALUE/root/value)
' );
Query 2:
SELECT xmlserialize(content COLUMN_VALUE as clob no indent) FROM XMLTABLE( '
count(collection("oradb:/TESTNG/TEST/ROW/VALUE")/root/value)'
)
Execution plan query 1:
Plan hash value: 1505571970
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 271K| 29 (0)| 00:00:01 |
| 1 | VIEW | | 8168 | 271K| 29 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 2004 | | |
| 3 | NESTED LOOPS | | 79M| 148G| 264K (1)| 00:52:57 |
|* 4 | TABLE ACCESS FULL | TEST | 9734 | 18M| 144 (0)| 00:00:02 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | 8168 | 16336 | 27 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("SYS_ORAVW_6"."SYS_NC00003$" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
Execution plan query 2:
Plan hash value: 857510304
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 271K| 29 (0)| 00:00:01 |
| 1 | VIEW | | 8168 | 271K| 29 (0)| 00:00:01 |
| 2 | SORT AGGREGATE | | 1 | 2004 | | |
| 3 | NESTED LOOPS | | 79M| 148G| 264K (1)| 00:52:57 |
|* 4 | TABLE ACCESS FULL| TEST | 9734 | 18M| 144 (0)| 00:00:02 |
| 5 | XPATH EVALUATION | | | | | |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("SYS_FNCOLL_6"."SYS_NC00003$" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
- Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)