The success or downfall of your application is determined by proper logical and physical design, especially if it is based on stored XML data in an Oracle database. The correct physical design of your XML data storage is vital for the flexibility and overall performance of your XML driven application architecture. Some key factors concerning performance are issues like data selectivity, retrieval of data via optimized execution paths and the total amount of data fetched.
Equipartitioning by Reference is one of those new 11gR1 XMLDB database partitioning features, which can help you achieve performance improvement via optimized data storage. Oracle XMLDB Equi-Partitioning will be available in the next database 11.1.0.7.0 patch release this year.
Equi-Partitioning by Reference can be used in situations like the "Orders" and "Line Items" problem. A master-detail situation where you can partition the master table by a reference of you liking that, will cause the detail table to be partitioned as defined by its keys in the master table. This can be done, because of the primary key, foreign key relationship between both tables. Like this can be achieved in 11g for "standard" tables, this can also be applied based on nested table structures during the design of XML instance storage planning.
Structured Object Relational (aka shredded) stored XML data can be made more selective in nature, splitting XML parts up in out of line storage, so called nested table structures. Normally partitioning would only add functionality regarding maintenance, but in this case it can be used to speed up more selective data retrieval, because this opens up the availability for the Oracle query Optimizer Engine to re-write queries when appropriate.
Have a look at the following XML Schema.
Setting up the XML structures
Given this the following can now be achieved, via XML Equi-partitioning by Reference, based on Employee and his/her corresponding ProjectDetails.
/*——————————————-+
|
|
| 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.
After registering the XML Schema and making use of the correct XDB annotations (e.g. xdb:maintainDOM="false"), equi-partitioning can be done via the following statement by taking EMPLOYEE_REFERENCE_ID as the reference to split up the partitions accordingly:
— 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
)
Selectiing the data
If you insert some small test data and query the structures, the following can be observed:
SQL> select * from user_part_tables;
TABLE_NAME
PARTITION SUBPART PARTITION_COUNT DEF_SUBPARTITION_COUNT PARTITIONING_KEY_COUNT
—————————— ——— ——- —————
———————- ———————-
SUBPARTITIONING_KEY_COUNT STATUS
DEF_TABLESPACE_NAME
DEF_PCT_FREE DEF_PCT_USED DEF_INI_TRANS DEF_MAX_TRANS
————————- ——–
—————————— ———— ———— ————-
————-
DEF_INITIAL_EXTENT
DEF_NEXT_EXTENT
DEF_MIN_EXTENTS
—————————————-
—————————————- —————————————-
DEF_MAX_EXTENTS
DEF_MAX_SIZE
DEF_PCT_INCREASE
—————————————-
—————————————-
—————————————-
DEF_FREELISTS DEF_FREELIST_GROUPS DEF_LOG DEF_COMP
DEF_COMPRESS_FOR DEF_BUF REF_PTN_CONSTRAINT_NAME
————- ——————- ——- ——–
—————— ——- ——————————
INTERVAL
—————————————————————————————————————————–
EMP_PROJ_TAB
REFERENCE
NONE
2
0
1
0 VALID USERS
10
1 255
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
0
0 NONE
NONE
DEFAULT SYS_FK0000070242C00012$
PART_DEMO
RANGE
NONE
2
0
1
0 VALID USERS
10
1 255
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
0
0 NONE
NONE
DEFAULT
PROJ_DETAILS_TAB
REFERENCE
NONE
2
0
1
0 VALID
USERS
10
1 255
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
0
0 NONE
NONE
DEFAULT SYS_FK0000070245C00003$
3 rows 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.
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_VALUE
—————————— —
—————————— —————— ——— ———-
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.
— 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 get
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
Extra check of the functionality
To demonstrate further that the equi-partitioning by reference worked I inserted an extra row.
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>
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.
Finishing up
The new Equi-Partitioning by Reference option will enable new strategies in easing constraints while handling with XML data. It will provide an extra method to be more selective and minimize traversal of XML structures while targeting the data needed. The sample script as demonstrated in this post can be downloaded via clicking the URL here: "XML Equi-Partitioning by Reference.txt"
Disclaimer
The information demonstrated and shared here
is based on Oracle new functionality and not available in the current release of Oracle 11gR1 (V.11.1.0.6.0). The following is intended to outline
Oracle’s general product direction. It is intended for information
purposes only, and may not be incorporated into any contract. It is
not a commitment to deliver any material, code, or functionality, and
should not be relied upon in making purchasing decisions. The
development, release, and timing of any features or functionality
described for Oracle’s products remains at the sole discretion of
Oracle.
