/*
   M. Gralike, AMIS Services BV, 02/01/2008
   Equipartitioning of XML nested table data.
*/

-- Creating a test user

SQL> connect sys as sysdba
Connected.

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL> drop user test cascade;
drop user test cascade
          *
ERROR at line 1:
ORA-01918: user 'TEST' does not exist

SQL> create user test identified by test;

User created.

SQL> grant xdbadmin, dba to test;

Grant succeeded.

SQL> connect test/test
Connected.

SQL> set long 100000000

SQL> purge recyclebin;

Recyclebin purged.

SQL> alter session set recyclebin=OFF;

Session altered.

SQL> alter session set events='31098 trace name context forever';

Session altered.

-- Registering the XML Schema with default varchar dbms_xmlschema.registerschema values

SQL> begin
  2  dbms_xmlschema.registerschema(
  3  'EmpProjectInfo.xsd',
  4  '<?xml version="1.0" encoding="UTF-8"?>
  5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" xdb:storeVarrayAsTable="false">
  6    <xs:element name="EmployeeProjectInfo" type="EmpType"/>
  7    <!-- does not have maintainDom=false -->
  8    <xs:complexType name="EmpType">
  9       <xs:sequence>
 10          <xs:element name="Reference" type="xs:positiveInteger" xdb:SQLName="EMPLOYEE_REFERENCE_ID"/>
 11          <xs:element name="Employees" type="EmployeesType" xdb:SQLName="EMPLOYEES"/>
 12        </xs:sequence>
 13      </xs:complexType>
 14    <xs:complexType name="EmployeesType" xdb:SQLType="XDB_EMPLOYEES_TYPE" xdb:maintainDOM="false">
 15       <xs:sequence>
 16          <xs:element name="Employee" maxOccurs="unbounded" xdb:SQLName="EMPLOYEE" xdb:maintainOrder="false" xdb:SQLCollType="XDB_EMPLOYEE_COLLECTION">
 17              <xs:complexType>
 18                 <xs:complexContent>
 19                    <xs:extension base="EmployeeType">
 20                       <xs:attribute name="id"/>
 21                    </xs:extension>
 22                 </xs:complexContent>
 23              </xs:complexType>
 24           </xs:element>
 25       </xs:sequence>
 26    </xs:complexType>
 27    <xs:complexType name="EmployeeType" xdb:SQLType="XDB_EMPLOYEE_TYPE" xdb:maintainDOM="false">
 28       <xs:sequence>
 29          <xs:element name="ProjectDetails" maxOccurs="unbounded" xdb:SQLName="PROJECT_DETAILS" xdb:maintainOrder="false" xdb:SQLCollType="XDB_DETAILS_COLLECTION">
 30              <xs:complexType>
 31                 <xs:complexContent>
 32                    <xs:extension base="ProjectDetails">
 33                       <xs:attribute name="id"/>
 34                    </xs:extension>
 35                 </xs:complexContent>
 36              </xs:complexType>
 37           </xs:element>
 38       </xs:sequence>
 39    </xs:complexType>
 40    <xs:complexType name="ProjectDetails" xdb:SQLType="XDB_PROJECT_DETAILS_TYPE" xdb:maintainDOM="false">
 41       <xs:sequence>
 42          <xs:element name="ProjectNumber" type="xs:positiveInteger" xdb:SQLName="PROJECT_NUMBER"/>
 43          <xs:element name="ProjectHours" type="xs:integer" xdb:SQLName="PROJECT_HOURS"/>
 44          <xs:element name="ProjectFunction" type="xs:string" xdb:SQLName="PROJECT_FUNCTION"/>
 45      </xs:sequence>
 46    </xs:complexType>
 47  </xs:schema>');
 48  END;
 49  /

PL/SQL procedure successfully completed.

-- User objects created

SQL> select * from user_objects;

OBJECT_NAME                                        SUBOBJECT_NAME                 OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------- ------------------------------ --------- -------------- -------------------
CREATED            LAST_DDL_TIME      TIMESTAMP           STATUS  T G S NAMESPACE EDITION_NAME
------------------ ------------------ ------------------- ------- - - - --------- ------------------------------
XDB_PROJECT_DETAILS_TYPE                                                              70220                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

ProjectDetails652_T                                                                   70221                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

XDB_DETAILS_COLLECTION                                                                70222                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

XDB_EMPLOYEE_TYPE                                                                     70223                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

Employee653_T                                                                         70224                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

XDB_EMPLOYEE_COLLECTION                                                               70225                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

XDB_EMPLOYEES_TYPE                                                                    70226                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

EmpType654_T                                                                          70227                TYPE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N N N         1

EmployeeProjectInfo655_TAB                                                            70228          70228 TABLE
02-JAN-08          02-JAN-08          2008-01-02:16:17:00 VALID   N Y N         1

SYSNTH9VFj9ykTZ2/a5L9qHhd/g==                                                         70229          70229 TABLE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         1

SYSNTHvj89wtDROGoDFsAArfZlQ==                                                         70230          70230 TABLE
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         1

SYS_C009924                                                                           70231          70231 INDEX
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         4

SYS_C009925                                                                           70238          70238 INDEX
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         4

SYS_LOB0000070228C00008$$                                                             70236          70236 LOB
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         8

SYS_LOB0000070228C00005$$                                                             70234          70234 LOB
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         8

SYS_LOB0000070228C00004$$                                                             70232          70232 LOB
02-JAN-08          02-JAN-08          2008-01-02:16:16:59 VALID   N Y N         8

SYS_C009926                                                                           70239          70239 INDEX
02-JAN-08          02-JAN-08          2008-01-02:16:17:00 VALID   N Y N         4

EmployeeProjectInfo655_TAB$xd                                                         70240                TRIGGER
02-JAN-08          02-JAN-08          2008-01-02:16:17:00 VALID   N N N         3


18 rows selected.

-- Creating the Equipartitioning object structures

SQL> CREATE TABLE PART_DEMO OF SYS.XMLTYPE
  2    XMLSCHEMA "EmpProjectInfo.xsd" ELEMENT "EmployeeProjectInfo"
  3  nested table xmldata."EMPLOYEES"."EMPLOYEE" STORE AS EMP_PROJ_TAB
  4      (nested table PROJECT_DETAILS STORE AS PROJ_DETAILS_TAB)
  5  partition by range( xmldata."EMPLOYEE_REFERENCE_ID")
  6  (partition P11 values less than (50)
  7  NESTED TABLE xmldata."EMPLOYEES"."EMPLOYEE" STORE AS EMP_PROJ_P11 (STORAGE (MINEXTENTS 13)
  8     nested table PROJECT_DETAILS STORE AS PROJ_DETAILS_P11),
  9  partition P21 values less than (MAXVALUE)
 10  NESTED TABLE xmldata."EMPLOYEES"."EMPLOYEE" STORE AS EMP_PROJ_P21 (STORAGE (MINEXTENTS 13)
 11      nested table PROJECT_DETAILS STORE AS PROJ_DETAILS_P21)
 12  );

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','PART_DEMO') from dual;

DBMS_METADATA.GET_DDL('TABLE','PART_DEMO')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."PART_DEMO" OF "SYS"."XMLTYPE"
XMLSCHEMA "EmpProjectInfo.xsd" ELEMENT "EmployeeProjectInfo"
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
NESTED TABLE "XMLDATA"."EMPLOYEES"."EMPLOYEE" STORE AS "EMP_PROJ_TAB"
 (PCTFREE 0 PCTUSED 0 INITRANS 0 MAXTRANS 0 LOGGING
  TABLESPACE "SYSTEM"
 NESTED TABLE "PROJECT_DETAILS" STORE AS "PROJ_DETAILS_TAB"
 (PCTFREE 0 PCTUSED 0 INITRANS 0 MAXTRANS 0 LOGGING
  TABLESPACE "SYSTEM" ) RETURN AS VALUE) RETURN AS VALUE
  PARTITION BY RANGE ("XMLDATA"."EMPLOYEE_REFERENCE_ID")
 (PARTITION "P11"  VALUES LESS THAN (50)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS ,
 PARTITION "P21"  VALUES LESS THAN (MAXVALUE)
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT
  )
 TABLESPACE "USERS" NOCOMPRESS
 )

1 row selected.

-- Creating simple test data

SQL> insert into "PART_DEMO"
  2  values
  3  (xmltype(
  4  '<EmployeeProjectInfo>
  5     <Reference>1</Reference>
  6     <Employees>
  7       <Employee id="111">
  8         <ProjectDetails>
  9           <ProjectNumber>200801010001</ProjectNumber>
 10           <ProjectHours>38</ProjectHours>
 11           <ProjectFunction>Project DBA</ProjectFunction>
 12        </ProjectDetails>
 13       </Employee>
 14     </Employees>
 15  </EmployeeProjectInfo>'));

1 row created.

SQL> insert into "PART_DEMO"
  2  values
  3  (xmltype(
  4  '<EmployeeProjectInfo>
  5     <Reference>51</Reference>
  6     <Employees>
  7       <Employee id="001">
  8         <ProjectDetails>
  9           <ProjectNumber>200801010001</ProjectNumber>
 10           <ProjectHours>24</ProjectHours>
 11           <ProjectFunction>Project Manager</ProjectFunction>
 12        </ProjectDetails>
 13       </Employee>
 14     </Employees>
 15  </EmployeeProjectInfo>'));

1 row created.

SQL> commit;

Commit complete.

-- Creating schema wide stats

SQL> exec dbms_stats.gather_schema_stats('TEST')

PL/SQL procedure successfully completed.

SQL> set autotrace on

-- Select examples and outcome

SQL> select u.EMPLOYEENUMBER
  2  from   "PART_DEMO" t
  3  ,      XMLTABLE('/EmployeeProjectInfo/Employees'
  4                  PASSING value(t)
  5                  COLUMNS employeenumber VARCHAR2(15) PATH 'Employee/@id'
  6                  ) u
  7  ;

EMPLOYEENUMBER
---------------
111
001

2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 493488300

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     2 |    38 |     4   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |              |     1 |    38 |            |          |       |       |
|   2 |   PARTITION REFERENCE SINGLE|              |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL        | EMP_PROJ_TAB |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
|   4 |  PARTITION RANGE ALL        |              |     2 |    38 |     4   (0)| 00:00:01 |     1 |     2 |
|*  5 |   TABLE ACCESS FULL         | PART_DEMO    |     2 |    38 |     4   (0)| 00:00:01 |     1 |     2 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("NESTED_TABLE_ID"=:B1)
   5 - filter("T"."SYS_NC00011$" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        169  consistent gets
          0  physical reads
          0  redo size
        458  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed


SQL> select u.projectdetails
  2  from   "PART_DEMO" t
  3  ,      XMLTABLE('//Employee'
  4                  PASSING value(t)
  5                  COLUMNS projectdetails XMLTYPE PATH '*'
  6                  ) u
  7  ;

PROJECTDETAILS
----------------------------------------------------------------------
<ProjectDetails>
  <ProjectNumber>200801010001</ProjectNumber>
  <ProjectHours>38</ProjectHours>
  <ProjectFunction>Project DBA</ProjectFunction>
</ProjectDetails>

<ProjectDetails>
  <ProjectNumber>200801010001</ProjectNumber>
  <ProjectHours>24</ProjectHours>
  <ProjectFunction>Project Manager</ProjectFunction>
</ProjectDetails>


2 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1750243350

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     2 |   150 |    36   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                     |                  |     1 |    42 |            |          |       |       |
|   2 |   PARTITION REFERENCE SINGLE        |                  |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL                | PROJ_DETAILS_TAB |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   4 |  NESTED LOOPS                       |                  |       |       |            |          |       |       |
|   5 |   NESTED LOOPS                      |                  |     2 |   150 |    36   (0)| 00:00:01 |       |       |
|   6 |    PARTITION REFERENCE ALL          |                  |     2 |    68 |    34   (0)| 00:00:01 |     1 |     2 |
|   7 |     TABLE ACCESS FULL               | EMP_PROJ_TAB     |     2 |    68 |    34   (0)| 00:00:01 |     1 |     2 |
|*  8 |    INDEX UNIQUE SCAN                | SYS_C009929      |     1 |       |     0   (0)| 00:00:01 |       |       |
|   9 |   TABLE ACCESS BY GLOBAL INDEX ROWID| PART_DEMO        |     1 |    41 |     1   (0)| 00:00:01 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("NESTED_TABLE_ID"=:B1)
   8 - access("NESTED_TABLE_ID"="T"."SYS_NC0001200013$")


Statistics
----------------------------------------------------------
         64  recursive calls
          0  db block gets
        248  consistent gets
          0  physical reads
          0  redo size
       2859  bytes sent via SQL*Net to client
       1620  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> select u.EMPLOYEENUMBER
  2  from   "PART_DEMO" t
  3  ,      XMLTABLE('/EmployeeProjectInfo/Employees'
  4                  PASSING value(t)
  5                  COLUMNS employeenumber VARCHAR2(15) PATH 'Employee/@id'
  6                  ) u
  7  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 51
  8  ;

EMPLOYEENUMBER
---------------
001

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3531522478

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    22 |     3   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE             |              |     1 |    38 |            |          |       |       |
|   2 |   PARTITION REFERENCE SINGLE|              |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL        | EMP_PROJ_TAB |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
|   4 |  PARTITION RANGE SINGLE     |              |     1 |    22 |     3   (0)| 00:00:01 |     2 |     2 |
|*  5 |   TABLE ACCESS FULL         | PART_DEMO    |     1 |    22 |     3   (0)| 00:00:01 |     2 |     2 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("NESTED_TABLE_ID"=:B1)
   5 - filter("T"."SYS_NC00009$"=51 AND "T"."SYS_NC00011$" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        131  consistent gets
          0  physical reads
          0  redo size
        418  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select *
  2  from "PART_DEMO" t
  3  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 1
  4  ;

SYS_NC_ROWINFO$
----------------------------------------------------------------------
<EmployeeProjectInfo>
  <Reference>1</Reference>
  <Employees>
    <Employee id="111">
      <ProjectDetails>
        <ProjectNumber>200801010001</ProjectNumber>
        <ProjectHours>38</ProjectHours>
        <ProjectFunction>Project DBA</ProjectFunction>
      </ProjectDetails>
    </Employee>
  </Employees>
</EmployeeProjectInfo>


1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3358334024

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                  |     1 |    41 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE|                  |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL        | PROJ_DETAILS_TAB |     1 |    42 |     3   (0)| 00:00:01 |   KEY |   KEY |
|   3 |  PARTITION REFERENCE SINGLE|                  |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  4 |   TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    38 |    18   (0)| 00:00:01 |   KEY |   KEY |
|   5 |  PARTITION RANGE SINGLE    |                  |     1 |    41 |     3   (0)| 00:00:01 |     1 |     1 |
|*  6 |   TABLE ACCESS FULL        | PART_DEMO        |     1 |    41 |     3   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NESTED_TABLE_ID"=:B1)
   4 - filter("NESTED_TABLE_ID"=:B1)
   6 - filter("T"."SYS_NC00009$"=1)


Statistics
----------------------------------------------------------
        184  recursive calls
          0  db block gets
        179  consistent gets
          0  physical reads
          0  redo size
      27109  bytes sent via SQL*Net to client
       5616  bytes received via SQL*Net from client
         53  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select extractvalue(value(t),'//ProjectFunction') as "PROJECT_FUNCTION"
  2  from "PART_DEMO" t
  3  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 1;

PROJECT_FUNCTION
----------------
Project DBA

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3474034851

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    20 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE |                  |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL         | PROJ_DETAILS_TAB |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |  FILTER                     |                  |       |       |            |          |       |       |
|   4 |   PARTITION REFERENCE SINGLE|                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |    TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|   6 |  PARTITION RANGE SINGLE     |                  |     1 |    20 |     3   (0)| 00:00:01 |     1 |     1 |
|*  7 |   TABLE ACCESS FULL         | PART_DEMO        |     1 |    20 |     3   (0)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NESTED_TABLE_ID"=:B1)
   3 - filter( (SELECT "SYS_ALIAS_4"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_4"
              WHERE "NESTED_TABLE_ID"=:B1) IS NOT NULL)
   5 - filter("NESTED_TABLE_ID"=:B1)
   7 - filter("T"."SYS_NC00009$"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         52  consistent gets
          0  physical reads
          0  redo size
        454  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select extractvalue(value(t),'//ProjectFunction') as "PROJECT_FUNCTION"
  2  from "PART_DEMO" t
  3  where extractvalue(value(t),'/EmployeeProjectInfo/Reference') = 51;

PROJECT_FUNCTION
----------------
Project Manager

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3474034851

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    20 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE |                  |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL         | PROJ_DETAILS_TAB |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |  FILTER                     |                  |       |       |            |          |       |       |
|   4 |   PARTITION REFERENCE SINGLE|                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |    TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|   6 |  PARTITION RANGE SINGLE     |                  |     1 |    20 |     3   (0)| 00:00:01 |     2 |     2 |
|*  7 |   TABLE ACCESS FULL         | PART_DEMO        |     1 |    20 |     3   (0)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NESTED_TABLE_ID"=:B1)
   3 - filter( (SELECT "SYS_ALIAS_4"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_4"
              WHERE "NESTED_TABLE_ID"=:B1) IS NOT NULL)
   5 - filter("NESTED_TABLE_ID"=:B1)
   7 - filter("T"."SYS_NC00009$"=51)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         52  consistent gets
          0  physical reads
          0  redo size
        458  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select extractvalue(value(t),'//ProjectHours') as "PROJECT_HOURS"
  2  from "PART_DEMO" t
  3  where  extractvalue(value(t),'//ProjectFunction') = 'Project Manager'
  4  ;

PROJECT_HOURS
-------------
           24

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2850148909

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     2 |    34 |    25   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE  |                  |     1 |    20 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL          | PROJ_DETAILS_TAB |     1 |    20 |     3   (0)| 00:00:01 |   KEY |   KEY |
|*  3 |  FILTER                      |                  |       |       |            |          |       |       |
|   4 |   PARTITION REFERENCE SINGLE |                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  5 |    TABLE ACCESS FULL         | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|*  6 |  FILTER                      |                  |       |       |            |          |       |       |
|   7 |   PARTITION RANGE ALL        |                  |     2 |    34 |     4   (0)| 00:00:01 |     1 |     2 |
|   8 |    TABLE ACCESS FULL         | PART_DEMO        |     2 |    34 |     4   (0)| 00:00:01 |     1 |     2 |
|*  9 |   FILTER                     |                  |       |       |            |          |       |       |
|  10 |    PARTITION REFERENCE SINGLE|                  |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|* 11 |     TABLE ACCESS FULL        | EMP_PROJ_TAB     |     1 |    34 |    18   (0)| 00:00:01 |   KEY |   KEY |
|  12 |    PARTITION REFERENCE SINGLE|                  |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
|* 13 |     TABLE ACCESS FULL        | PROJ_DETAILS_TAB |     1 |    31 |     3   (0)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("NESTED_TABLE_ID"=:B1)
   3 - filter( (SELECT "SYS_ALIAS_7"."PROJECT_HOURS" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_7" WHERE
              "NESTED_TABLE_ID"=:B1) IS NOT NULL)
   5 - filter("NESTED_TABLE_ID"=:B1)
   6 - filter( (SELECT  (SELECT "SYS_ALIAS_6"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB"
              "SYS_ALIAS_6" WHERE "NESTED_TABLE_ID"=:B1) FROM "TEST"."EMP_PROJ_TAB" "SYS_ALIAS_2" WHERE  (SELECT
              "SYS_ALIAS_6"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_6" WHERE
              "NESTED_TABLE_ID"=:B2) IS NOT NULL AND "NESTED_TABLE_ID"=:B3)='Project Manager')
   9 - filter( (SELECT "SYS_ALIAS_6"."PROJECT_FUNCTION" FROM "TEST"."PROJ_DETAILS_TAB" "SYS_ALIAS_6"
              WHERE "NESTED_TABLE_ID"=:B1) IS NOT NULL)
  11 - filter("NESTED_TABLE_ID"=:B1)
  13 - filter("NESTED_TABLE_ID"=:B1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        119  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> insert into "PART_DEMO"
  2  values
  3  (xmltype(
  4  '<EmployeeProjectInfo>
  5     <Reference>1</Reference>
  6     <Employees>
  7       <Employee id="111">
  8         <ProjectDetails>
  9           <ProjectNumber>200801010001</ProjectNumber>
 10           <ProjectHours>38</ProjectHours>
 11           <ProjectFunction>Project DBA</ProjectFunction>
 12        </ProjectDetails>
 13       </Employee>
 14     </Employees>
 15  </EmployeeProjectInfo>'));

1 row created.

SQL>
SQL> select table_name,
  2         composite,
  3         partition_name,
  4         subpartition_count,
  5         num_rows,
  6         high_value
  7  from   user_tab_partitions;

TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT  NUM_ROWS HIGH_VALU
------------------------------ --- ------------------------------ ------------------ --------- ---------
PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P11                                0         1
PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P21                                0         1
EMP_PROJ_TAB                   NO  EMP_PROJ_P11                                    0         1
EMP_PROJ_TAB                   NO  EMP_PROJ_P21                                    0         1
PART_DEMO                      NO  P11                                             0         1 50
PART_DEMO                      NO  P21                                             0         1 MAXVALUE

6 rows selected.

SQL> commit;

Commit complete.

SQL> select table_name,
  2         composite,
  3         partition_name,
  4         subpartition_count,
  5         num_rows,
  6         high_value
  7  from   user_tab_partitions;

TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT  NUM_ROWS HIGH_VALU
------------------------------ --- ------------------------------ ------------------ --------- ---------
PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P11                                0         1
PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P21                                0         1
EMP_PROJ_TAB                   NO  EMP_PROJ_P11                                    0         1
EMP_PROJ_TAB                   NO  EMP_PROJ_P21                                    0         1
PART_DEMO                      NO  P11                                             0         1 50
PART_DEMO                      NO  P21                                             0         1 MAXVALUE

6 rows selected.

SQL> exec dbms_stats.gather_schema_stats('TEST')

PL/SQL procedure successfully completed.

SQL> select table_name,
  2         composite,
  3         partition_name,
  4         subpartition_count,
  5         num_rows,
  6         high_value
  7  from   user_tab_partitions;

TABLE_NAME                     COM PARTITION_NAME                 SUBPARTITION_COUNT  NUM_ROWS HIGH_VALU
------------------------------ --- ------------------------------ ------------------ --------- ---------
PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P11                                0         2
PROJ_DETAILS_TAB               NO  PROJ_DETAILS_P21                                0         1
EMP_PROJ_TAB                   NO  EMP_PROJ_P11                                    0         2
EMP_PROJ_TAB                   NO  EMP_PROJ_P21                                    0         1
PART_DEMO                      NO  P11                                             0         2 50
PART_DEMO                      NO  P21                                             0         1 MAXVALUE

6 rows selected.


-- Cleaning up.

SQL> drop table PART_DEMO;

Table dropped.

SQL> call DBMS_XMLSCHEMA.deleteSchema('EmpProjectInfo.xsd',4);

Call completed.

SQL> conn sys as sysdba
Connected.

SQL> drop user test cascade;

User dropped.