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.

Marco Gralike Written by:

6 Comments

  1. sky_lt
    March 18

    Nice examples:

    Can you provide XML document example which refers to “http://www.server.com/XMLSchema_V1.0.xsd” schema

    Also is it possible to add additional virtual columns based on xml to those tables?

    Does row locking works on such tables? i mean “where current of” statements

    thanks

  2. March 18

    You will have to provide me with a schema first…

    Yes, it is possible to add virtual columns based on the xml xpath tree definition.

    Yes, Locking works as it should for that database version.

    • sky_lt
      March 18

      Hi Marco,

      I took that shema name “http://www.server.com/XMLSchema_V1.0.xsd” from your example and thought you have xml doc near by.

      I want to check once more:
      Currently i am planing to use XMLDB capabilities on new DB version (11.2) so i want to recheck:

      Is it posible to have additional indexed virtual columns on
      table which is list partitioned as in your examle table “TEST_LIST_XML”

      the ddl should be like(i can not test as i do not have 11.2g enviroment yet):
      CREATE TABLE TEST_LIST_XML
      ( “REGION” VARCHAR2(3),
      “DOC” “SYS”.”XMLTYPE”
      ) SEGMENT CREATION IMMEDIATE
      NOCOMPRESS NOLOGGING
      TABLESPACE “XML_DATA”
      XMLTYPE COLUMN “DOC” STORE AS SECUREFILE BINARY XML
      (TABLESPACE “XML_DATA”
      NOCOMPRESS KEEP_DUPLICATES)
      XMLSCHEMA “http://www.server.com/XMLSchema_V1.0.xsd”
      ELEMENT “ROOT”
      DISALLOW NONSCHEMA
      VIRTUAL COLUMNS
      (xmlrootid AS (extractvalue(object_value,’/ROOT/ID’)),
      xmldata AS (extractvalue(object_value,’/ROOT/ID/data/desc’)));
      PARTITION BY LIST(“REGION”)
      (PARTITION LIST_PART_01 VALUES (‘AAA’, ‘BBB’)
      ,PARTITION LIST_PART_02 VALUES (‘CCC’, ‘DDD’));

      Is it possible to use virtual columns(“xmlrootid” and “xmldata” in my example) in SQL queries or they ar designed only for constraints?

      Thanks

  3. April 11

    Yes this can be done, but

    a) You should use XMLEXISTS and XMLCAST to define your virtual columns in this 11.2 database version

    b) You should support these virtual columns via a structured XMLIndex or B-Tree unique index to make it performance wise reasonable

Comments are closed.