Something new? Eh? Should you do this? Eh?
In all, probably not, but for me this was a good exercise towards some more updated demo scripting for my “Boost your environment with XMLDB” presentation or hopefully more clearer relabeled Oracle Open World name for the almost same presentation called “Interfacing with Your Database via Oracle XML DB” (S319105). Just up front, there are some issues with the following:
- Why should you do it at all. You should have a good reason doing so…
- It can cause a lot of Physical I/O, at least initially when not cached in the SGA
- Until current versions, AFAIK, it will do a lot of “Pickler Fetching”, serializing in memory, which is very resource intensive (CPU/PGA)
- …and its probably not supported…?
…but it is good fun for a small exercise based on the following OTN Thread: “Error with basic XMLTable“…
Let me show you what I mean.
Via “bfilename” you are able, since a long time, I guess Oracle 9.2 and onwards, to read a file as a BLOB and because an “XMLTYPE” can swallow almost any datatype, you could do the following…
[oracle@localhost ~]$ sqlplus / AS sysdba
SQL*Plus: Release 11.2.0.1.0 Production ON Thu Jul 29 09:20:24 2010
Copyright (c) 1982, 2009, Oracle. ALL rights reserved.
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.1.0 - Production
WITH the Partitioning, OLAP, DATA Mining AND Real Application Testing options
SQL> CREATE user otn IDENTIFIED BY otn account UNLOCK;
User created.
SQL> GRANT dba TO otn;
GRANT succeeded.
SQL> conn otn/otn
Connected.
SQL> sho user
USER IS "OTN"
SQL> SET pages 5000
SQL> SET LINES 1000
SQL> SET long 10000
SQL> SELECT xmltype(cursor(SELECT owner, schema_url, LOCAL FROM all_xml_schemas ORDER BY owner)) FROM dual;
XMLTYPE(CURSOR(SELECTOWNER,SCHEMA_URL,LOCALFROMALL_XML_SCHEMASORDERBYOWNER))
-----------------------------------------------------------------------------------------------------------
< ?xml version="1.0"?>
<rowset>
<row>
<owner>EXFSYS</owner>
<schema_url>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>EXFSYS</owner>
<schema_url>http://xmlns.oracle.com/rlmgr/rulecond.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>MDSYS</owner>
<schema_url>http://www.opengis.net/gml/feature.xsd</schema_url>
<local>NO</local>
</row>
<row>
...
</row><row>
<owner>XDB</owner>
<schema_url>http://xmlns.oracle.com/xdb/dav.xsd</schema_url>
<local>NO</local>
</row>
</rowset>
51 rows selected.
Lets write the output to disk in the /tmp directory or my Oracle Enterprise Linux environment…
SQL> sho user USER IS "OTN" SQL> CREATE OR REPLACE directory XMLDIR AS '/tmp/'; Directory created. SQL> declare 2 rc sys_refcursor; 3 begin 4 open rc FOR SELECT * FROM (SELECT owner, schema_url, LOCAL FROM all_xml_schemas ORDER BY owner); 5 dbms_xslprocessor.clob2file(xmltype(rc).getClobVal(),'TMPDIR','otn_dev_xsd_schema.xml'); 6 end; 7 ; PL/SQL procedure successfully completed. SQL> commit; commit complete SQL> ! ls -ltra /tmp/*.xml -rw-rw-r-- 1 oracle oracle 6563 Jul 29 09:36 /tmp/otn_dev_xsd_schema.xml
Due to the fact the content of this XML file (be aware: 6K) is wellformed XML, for example the alert log is not wellformed, and I know its format, I could read it directly from my database session via the following…
SQL> SELECT xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) FROM dual;
XMLTYPE(BFILENAME('TMPDIR','OTN_DEV_XSD_SCHEMA.XML'),NLS_CHARSET_ID('AL32UTF8'))
---------------------------------------------------------------------------------------
< ?xml version="1.0"?>
<rowset>
<row>
<owner>EXFSYS</owner>
<schema_url>http://xmlns.oracle.com/rlmgr/rclsprop.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>EXFSYS</owner>
<schema_url>http://xmlns.oracle.com/rlmgr/rulecond.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>MDSYS</owner>
<schema_url>http://www.opengis.net/gml/feature.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>MDSYS</owner>
<schema_url>http://xmlns.oracle.com/spatial/georaster/georaster.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>MDSYS</owner>
<schema_url>http://www.w3.org/1999/xlink/xlinks.xsd</schema_url>
<local>NO</local>
</row>
<row>
<owner>MDSYS</owner>
<schema_url>http://www.opengis.net/gml/geometry.xsd</schema_url>
<local>NO</local>
</row>
 ...
 <row>
  <owner>XDB</owner>
<schema_url>http://xmlns.oracle.com/xdb/dav.xsd</schema_url>
<local>NO</local>
</row>
</rowset>
51 rows selected.
That easy, although as said it has some issues, performance wise. From Oracle database version 10.2.0.1 and onwards, although I would use as a minimum 10.2.0.3.0 due to that the XQuery engine is C, Oracle kernel build-in based, you could now use the XMLTABLE function, to handle it further. XMLTABLE is a great function to make convert XML in a more relational in look-and-feel and / or because it supports XQuery version 1.0 (and as you (should) know XQuery is a very powerful query language). Anyway, via XMLTABLE, we can now show data in a relational form is demonstrated via the following:
SQL> SELECT xtab.owner
2 , xtab.schema_url
3 , xtab.LOCAL
4 FROM (SELECT xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) OBJECT_VALUE FROM dual) xfile
5 , XMLTABLE ('/ROWSET/ROW'
6 PASSING xfile.object_value
7 COLUMNS OWNER VARCHAR2(30) path 'OWNER'
8 , SCHEMA_URL VARCHAR2(80) path 'SCHEMA_URL'
9 , LOCAL VARCHAR2(3) path 'LOCAL'
10 ) xtab
11 ;
OWNER SCHEMA_URL LOC
------------------------------ -------------------------------------------------------------------------------- ---
EXFSYS http://xmlns.oracle.com/rlmgr/rclsprop.xsd NO
EXFSYS http://xmlns.oracle.com/rlmgr/rulecond.xsd NO
MDSYS http://www.opengis.net/gml/feature.xsd NO
MDSYS http://xmlns.oracle.com/spatial/georaster/georaster.xsd NO
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd NO
MDSYS http://www.opengis.net/gml/geometry.xsd NO
ORDSYS http://xmlns.oracle.com/ord/dicom/preference_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/mapping_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/metadata_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/constraint_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/anonymity_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/meta/xmp NO
ORDSYS http://xmlns.oracle.com/ord/dicom/standardDictionary_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/meta/iptc NO
ORDSYS http://xmlns.oracle.com/ord/dicom/mddatatype_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/orddicom_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/datatype_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/privateDictionary_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/meta/exif NO
ORDSYS http://xmlns.oracle.com/ord/dicom/rpdatatype_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/meta/ordimage NO
ORDSYS http://xmlns.oracle.com/ord/dicom/UIDdefinition_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/manifest_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/attributeTag_1_0 NO
...
XDB http://xmlns.oracle.com/xdb/XDBStandard.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBResConfig.xsd NO
XDB http://xmlns.oracle.com/xdb/acl.xsd NO
XDB http://xmlns.oracle.com/xdb/dav.xsd NO
51 rows selected.
…and due to the fact you can query it, you can create a view out of it…
SQL> CREATE or REPLACE view MY_EXTERNAL_XFILE
2 ( OWNER
3 , SCHEMA
4 , MINE
5 )
6 AS
7 SELECT xtab.owner
8 , xtab.schema_url
9 , xtab.local
10 FROM (select xmltype(bfilename('TMPDIR','otn_dev_xsd_schema.xml'), nls_charset_id('AL32UTF8')) OBJECT_VALUE from dual) xfile
11 , XMLTABLE ('/ROWSET/ROW'
12 PASSING xfile.object_value
13 COLUMNS OWNER VARCHAR2(30) path 'OWNER'
14 , SCHEMA_URL VARCHAR2(80) path 'SCHEMA_URL'
15 , LOCAL VARCHAR2(3) path 'LOCAL'
16 ) xtab
17 ;
View created.
SQL> desc my_external_xfile
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
SCHEMA VARCHAR2(80)
MINE VARCHAR2(3)
SQL> select * from MY_EXTERNAL_XFILE
2 ;
OWNER SCHEMA MIN
------------------------------ -------------------------------------------------------------------------------- ---
EXFSYS http://xmlns.oracle.com/rlmgr/rclsprop.xsd NO
EXFSYS http://xmlns.oracle.com/rlmgr/rulecond.xsd NO
MDSYS http://www.opengis.net/gml/feature.xsd NO
MDSYS http://xmlns.oracle.com/spatial/georaster/georaster.xsd NO
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd NO
MDSYS http://www.opengis.net/gml/geometry.xsd NO
ORDSYS http://xmlns.oracle.com/ord/dicom/preference_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/mapping_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/metadata_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/constraint_1_0 NO
ORDSYS http://xmlns.oracle.com/ord/dicom/anonymity_1_0 NO
...
XDB http://xmlns.oracle.com/xdb/XDBStandard.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBResConfig.xsd NO
XDB http://xmlns.oracle.com/xdb/acl.xsd NO
XDB http://xmlns.oracle.com/xdb/dav.xsd NO
51 rows selected.
So now we have a “standard” relational view that we can query, for example, show me all “w3” based in the XDB Repository registered XML Schemas which are not mine, but accessible for all in the database, via…
SQL> select * from MY_EXTERNAL_XFILE where owner='XDB' 2 and instr(SCHEMA,'w3') > 0 3 ; OWNER SCHEMA MIN ------------------------------ -------------------------------------------------------------------------------- --- XDB http://www.w3.org/2001/csx.xml.xsd NO XDB http://www.w3.org/2001/csx.XInclude.xsd NO XDB http://www.w3.org/2001/XInclude.xsd NO XDB http://www.w3.org/1999/csx.xlink.xsd NO XDB http://www.w3.org/1999/xlink.xsd NO XDB http://www.w3.org/2001/xml.xsd NO 6 rows selected.
Price to pay…?
As said, it comes with a price to pay, regarding I/O and other performance issues. For example check out the following stats, initially via “explain plan”…
SQL> conn / as sysdba
Connected.
SQL> startup
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 251661400 bytes
Database Buffers 54525952 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> set autotrace on
SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
2 and instr(SCHEMA,'w3') > 0;
OWNER SCHEMA MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd NO
Execution Plan
----------------------------------------------------------
Plan hash value: 2888251253
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 44 (30)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 8 | 44 (30)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 3 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 4 | 8 | 42 (31)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
Statistics
----------------------------------------------------------
3624 recursive calls
0 db block gets
2888 consistent gets
105 physical reads
0 redo size
583 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
143 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
2 and instr(SCHEMA,'w3') > 0;
OWNER SCHEMA MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd NO
Execution Plan
----------------------------------------------------------
Plan hash value: 2888251253
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8 | 44 (30)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 8 | 44 (30)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 3 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 4 | 8 | 42 (31)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,209715
20,0),50,1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VA
LUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
583 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
…and a second time via the same “procedure” checking via the stats via DBMS_XPLAN…
SQL> startup force
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1336232 bytes
Variable Size 251661400 bytes
Database Buffers 54525952 bytes
Redo Buffers 6336512 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> conn otn/otn
Connected.
SQL> alter session set statistics_level=all;
Session altered.
SQL> create or replace view xplan as select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
View created.
SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
2 and instr(SCHEMA,'w3') > 0;
SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
2 and instr(SCHEMA,'w3') > 0;
OWNER SCHEMA MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd NO
SQL> select * from xplan;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID 9kc32mbtas765, child number 0
-------------------------------------
select * from MY_EXTERNAL_XFILE where owner='MDSYS' and
instr(SCHEMA,'w3') > 0
Plan hash value: 2888251253
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.09 | 959 | 52 |
| 1 | NESTED LOOPS | | 1 | 4 | 1 |00:00:00.09 | 959 | 52 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | 0 |
|* 3 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 1 | 4 | 1 |00:00:00.08 | 959 | 52 |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,20971520,0),50,1,2) AS
VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,2
0971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0))
23 rows selected.
SQL> select * from MY_EXTERNAL_XFILE where owner='MDSYS'
2 and instr(SCHEMA,'w3') > 0;
OWNER SCHEMA MIN
------------------------------ -------------------------------------------------------------------------------- ---
MDSYS http://www.w3.org/1999/xlink/xlinks.xsd NO
SQL> select * from xplan;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 9kc32mbtas765, child number 0
-------------------------------------
select * from MY_EXTERNAL_XFILE where owner='MDSYS' and
instr(SCHEMA,'w3') > 0
Plan hash value: 2888251253
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 3 |
| 1 | NESTED LOOPS | | 1 | 4 | 1 |00:00:00.01 | 3 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 3 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 1 | 4 | 1 |00:00:00.01 | 3 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,20971520,0),50,
1,2) AS VARCHAR2(30) )='MDSYS' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/S
CHEMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')>0))
23 rows selected.
As said before, initially Oracle has to do a lot of disk reads and all has to be done via “COLLECTION ITERATOR PICKLER FETCH”, which means in memory, but although in memory, Oracle is able to rewrite it via the XQuery engine (mark the “XQ” in the filter statements) to their relational counter parts due to the fact that Oracle got this information via the XMLTABLE function definition.
The second time around everything has been cached. See the following output via all statistics / DBMS_XPLAN…
SQL> select * from MY_EXTERNAL_XFILE where owner='XDB'
2 and instr(SCHEMA,'w3') = 0
3 ;
OWNER SCHEMA MIN
------------------------------ -------------------------------------------------------------------------------- ---
XDB http://xmlns.oracle.com/xdb/csx.xmltr.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBResource.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBSchema.xsd NO
XDB http://xmlns.oracle.com/xs/principal.xsd NO
XDB http://xmlns.oracle.com/xs/aclids.xsd NO
XDB http://xmlns.oracle.com/xs/dataSecurity.xsd NO
XDB http://xmlns.oracle.com/xdb/xdbconfig.xsd NO
XDB http://xmlns.oracle.com/xs/securityclass.xsd NO
XDB http://xmlns.oracle.com/xs/roleset.xsd NO
XDB http://xmlns.oracle.com/xdb/stats.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBFolderListing.xsd NO
XDB http://xmlns.oracle.com/xdb/xmltr.xsd NO
XDB http://xmlns.oracle.com/xdb/log/httplog.xsd NO
XDB http://xmlns.oracle.com/xdb/log/ftplog.xsd NO
XDB http://xmlns.oracle.com/xdb/log/xdblog.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBStandard.xsd NO
XDB http://xmlns.oracle.com/xdb/XDBResConfig.xsd NO
XDB http://xmlns.oracle.com/xdb/acl.xsd NO
XDB http://xmlns.oracle.com/xdb/dav.xsd NO
19 rows selected.
SQL> select * from xplan;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID bjx9wfv2hp6u7, child number 0
-------------------------------------
select * from MY_EXTERNAL_XFILE where owner='XDB' and
instr(SCHEMA,'w3') = 0
Plan hash value: 2888251253
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 19 |00:00:00.03 | 3 |
| 1 | NESTED LOOPS | | 1 | 1 | 19 |00:00:00.03 | 3 |
| 2 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 |
|* 3 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 1 | 1 | 19 |00:00:00.03 | 3 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/OWNER'),0,0,20971520,0),50,
1,2) AS VARCHAR2(30) )='XDB' AND INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/SCH
EMA_URL'),0,0,20971520,0),50,1,2) AS VARCHAR2(80) ),'w3')=0))
23 rows selected.
SQL> select owner, count(*)
2 from MY_EXTERNAL_XFILE
3 where instr(SCHEMA,'xmlns.oracle.com') > 0
4 group by owner;
OWNER COUNT(*)
------------------------------ ----------
MDSYS 1
EXFSYS 2
ORDSYS 19
XDB 19
SYS 1
SQL> select * from xplan;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5sqvj1k6pmmxj, child number 0
-------------------------------------
select owner, count(*) from MY_EXTERNAL_XFILE where
instr(SCHEMA,'xmlns.oracle.com') > 0 group by owner
Plan hash value: 2770558904
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.03 | 3 | | | |
| 1 | HASH GROUP BY | | 1 | 408 | 5 |00:00:00.03 | 3 | 855K| 855K| 917K (0)|
| 2 | NESTED LOOPS | | 1 | 408 | 42 |00:00:00.02 | 3 | | | |
| 3 | FAST DUAL | | 1 | 1 | 1 |00:00:00.01 | 0 | | | |
|* 4 | COLLECTION ITERATOR PICKLER FETCH| XMLSEQUENCEFROMXMLTYPE | 1 | 408 | 42 |00:00:00.02 | 3 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(INSTR(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXTRACT(VALUE(KOKBF$),'/ROW/SCHEMA_URL'),0,0,20971520,0),50,1,2) AS
VARCHAR2(80) ),'xmlns.oracle.com')>0)
23 rows selected.
Who knows with a little bit of fantasy, me or you, could find a case this could be applied and makes sense. This example was based on the following OTN Thread: “Error with basic XMLTable” and its probably easier to read up it over there. Hope you liked it.
