11g Technology Preview: XML Data Retrieval via Equi Partitioning by Reference

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.

....
 

11g Technology Preview: XML Data Retrieval via Equi Partitioning by Reference

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"                                                 
 

values                                                                  
 
3  (xmltype(                                     
                          
 

‘<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.