External Views (XML based) startup 594127 1280

External Views (XML based)

July 29, 2010 IT, XML No Comments

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.

Related Posts

About The Author

Marco Gralike

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance. He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).