/*
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 '
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47 ');
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 '
5 1
6
7
8
9 200801010001
10 38
11 Project DBA
12
13
14
15 '));
1 row created.
SQL> insert into "PART_DEMO"
2 values
3 (xmltype(
4 '
5 51
6
7
8
9 200801010001
10 24
11 Project Manager
12
13
14
15 '));
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
----------------------------------------------------------------------
200801010001
38
Project DBA
200801010001
24
Project Manager
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$
----------------------------------------------------------------------
1
200801010001
38
Project DBA
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 '
5 1
6
7
8
9 200801010001
10 38
11 Project DBA
12
13
14
15 '));
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.