HOWTO: Partition Binary XML, XMLType Storage

Just to start off the 11gR2 series, here the first small “HOWTO” post regarding the new features in Oracle 11gR2. The 11gR2 database supports now 3 binary XML partitioning options. Partitioning has great advantages, if you are dealing with huge volumes of XML data. Those advantages are mainly regarding maintenance, for instance updating/refreshing data, or the local partitioning index that belongs to the specific partition that you want to alter. You can use this form of partitioning on XMLType Columns and XMLType Tables, XML Schema based or Schema-less.

Binary XML XMLType should be used, most of the time, if you are dealing with content/data driven environments (XML Schema based) or document driven (if XML Schema less). The following 3 options are now available:

1. range
2. list
3. hash

XML Partitioning is based on a column or a virtual column and is defined via the XMLTABLE function/syntax. You are allowed to pass on a XML resultset/fragment into the next nested XMLTABLE syntax, but only once. See the Oracle XMLDB Developers Guides for more information.

Range Partitioning

The following is an example of a XMLType Column (based on Binary XML) stored table, where a column is defined that will be used to partition the table. A virtual column could also be used for this, but be aware that it should be indexed if you want to avoid performance issues.

SQL> CREATE TABLE TEST_RANGE_XML
  2   (    "ID" NUMBER(15,0),
  3        "DOC" "SYS"."XMLTYPE"
  4   ) SEGMENT CREATION IMMEDIATE
  5  	NOCOMPRESS NOLOGGING
  6  TABLESPACE "XML_DATA"
  7   	XMLTYPE COLUMN "DOC" STORE AS SECUREFILE BINARY XML
  8   	(TABLESPACE "XML_DATA"
  9		NOCOMPRESS  KEEP_DUPLICATES)
 10	XMLSCHEMA "http://www.server.com/XMLSchema_V1.0.xsd"
 11		ELEMENT "ROOT"
 12	DISALLOW NONSCHEMA
 13  PARTITION BY RANGE("ID") 
 14 (PARTITION RANGE_PART_01 VALUES LESS THAN  (100) TABLESPACE "XML_DATA"
 15 ,PARTITION RANGE_PART_02 VALUES LESS THAN  (200) TABLESPACE "XML_DATA")
 16 ,PARTITION RANGE_PART_MAX VALUES LESS THAN  (MAXVALUE)); 

List Partitioning

The following is an example of a XMLType Column (based on Binary XML) stored table, where a list has been defined that drives the partitioning of the XML documents.

SQL> CREATE TABLE TEST_LIST_XML
  2   (    "REGION" VARCHAR2(3),
  3        "DOC" "SYS"."XMLTYPE"
  4   ) SEGMENT CREATION IMMEDIATE
  5  	NOCOMPRESS NOLOGGING
  6  	TABLESPACE "XML_DATA"
  7		XMLTYPE COLUMN "DOC" STORE AS SECUREFILE BINARY XML
  8 		(TABLESPACE "XML_DATA"
  9    	NOCOMPRESS  KEEP_DUPLICATES)
 10	XMLSCHEMA "http://www.server.com/XMLSchema_V1.0.xsd"
 11		ELEMENT "ROOT"
 12	DISALLOW NONSCHEMA
 13  PARTITION BY LIST("REGION") 
 14 (PARTITION LIST_PART_01 VALUES ('AAA', 'BBB')
 15 ,PARTITION LIST_PART_02 VALUES ('CCC', 'DDD'));

Hash Partitioning

The following is an example of a XMLType Column (based on Binary XML) stored table, where a column is defined that will be used to hash partition the table. Be aware that this partitioning option can not (yet?) be used in combination with an “unstructured xmlindex” or “structured xmlindex”.

You are allowed to use an Oracle Text index to index the values defined by the elements in your XML document.

SQL> CREATE TABLE TEST_HASH_XML
  2   (    "ID" NUMBER(15,0),
  3        "DOC" "SYS"."XMLTYPE"
  4   ) SEGMENT CREATION IMMEDIATE
  5  	NOCOMPRESS NOLOGGING
  6  	TABLESPACE "XML_DATA"
  7   	XMLTYPE COLUMN "DOC" STORE AS SECUREFILE BINARY XML
  8   	(TABLESPACE "XML_DATA"
  9    	NOCOMPRESS  KEEP_DUPLICATES)
 10	XMLSCHEMA "http://www.server.com/XMLSchema_V1.0.xsd"
 11   	ELEMENT "ROOT"
 12	DISALLOW NONSCHEMA
 13  PARTITION BY HASH("ID") 
 14 (PARTITION "HASH_SECTION_01" TABLESPACE "XML_DATA_01" 
 15 ,PARTITION "HASH_SECTION_02" TABLESPACE "XML_DATA_02" 
 16 ,PARTITION "HASH_SECTION_03" TABLESPACE “XML_DATA_03" ); 

XMLIndex usage with Binary XML partitions

Binary XML has to be locally indexed. This is done via adding the “LOCAL” in your create XMLIndex syntax. This will trigger the creation of local XMLIndex structures for each partition of the XML table.

An syntax example:

SQL> CREATE INDEX xmlindex_sxi 
     on xmldata_table (doc) 
     indextype is xdb.xmlindex
     LOCAL 
     parameters 
       ('GROUP elementinfo_group 
         XMLTABLE xml_cnt_tab_elementinfo
         ' '/root/element' ' 
         COLUMNS 
         infocol VARCHAR2(4000) PATH ' 'info' '
       '); 

As said, see the Oracle XMLDB Developers Guides for more information.