There is a Metalink note (563802.1) that has a very good example how XML data storage, partitioning can be achieved in Oracle version 9.2.0.3.0 (the first officially supported XMLDB database functionality) and onwards. Partitioning can be used in such XML situations to spread the I/O load and it also supports, if done correctly, the XMLDB functionality / Oracle database, to use query re-writes during XPath or XQuery statements. See for more information about Query Rewrite support the Oracle XMLDB Developers Guide.
The “new” equi-partitioning functionality, supported from 11.1.0.7.0, but now described in more detail in the 11gR2 XMLDB Developers Manual, also propagates / partitions all underlying structures. As described in the Metalink note: “In Oracle release 11g the partitioning model is equi-partitioning which means that the underlying storage tables are partitioned too along with the default table partitioning structure. In previous releases only the default table is partitioned, but the underlying storage tables for the types are not.”
So explained in more detail, before 11.1.0.7.0 only the “xdb:defaultTable”, in the following example table “TESTELEMENT”, is partitioned by range. In Oracle 11.1.0.7.0 and onwards also all referred XML structures underneath this entry point are equally partitioned based on this defined range.
So following the Metalink note example:
To create partitions on a schema based XMLtype table, you have to define the partitions inside the XML schema by using the xdb:tableProps attribute.
Additionally the partition column name must be defined in capitals. If it is not in capital letters, a SQL name must be defined.
Following XML schema contains the definition for partitioning table TESTELEMENT:
<xs:schema version="1.0"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:element name="TestElement" type="TestElementType"
xdb:defaultTable="TESTELEMENT"
xdb:tableProps="PARTITION BY RANGE (XMLDATA.IDENTIFIER)
(PARTITION P01 VALUES LESS THAN (10) TABLESPACE users,
PARTITION P02 VALUES LESS THAN (20) TABLESPACE users,
PARTITION P03 VALUES LESS THAN (MAXVALUE) TABLESPACE users)"/>
<xs:complexType name="TestElementType">
<xs:sequence>
<xs:element name="Identifier" type="xs:int"
xdb:SQLName="IDENTIFIER"/>
<xs:element name="Person" type="xs:string"/>
<xs:element name="Street" type="xs:string"/>
<xs:element name="City" type="xs:string"/>
<xs:element name="State" type="xs:string"/>
<xs:element name="Zipcode" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:schema>
When registering the XML schema above, the partitions will be created:
begin
dbms_xmlschema.registerschema('http://schema.xsd', schema,
genTables=>true, genTypes=>true);
end;
/select table_name, partition_name from user_tab_partitions;
TABLE_NAME PARTITION_NAME
—————————— ——————————
TESTELEMENT P01
TESTELEMENT P02
TESTELEMENT P03
This example clearly demonstrates, IMHO, the advantages regarding maintenance, design and control for the created objects via a XML Schema. Via such an XML Schema the wanted DESIGN and PHYSICAL presentation of the wanted structures can be enforced and maintained in a more easy manner than via, most of the time, very complex manual SQL statements.
The mentioned example is based on an XMLType Object Relational storage table. This physical storage structure is very performant in situations where you XML is “Content Driven” and is a very XML structured in nature. Translated into “relational” definitions and context, these definitions could be explained as “the XML is almost relational in structure and its data access is always driven on parts of the info contained in the XML”.
A disadvantage of such a XMLType table (object relationnal storage based) is that it is driven / depending on a XML Schema (and therefore a trade off regarding CPU time etc for XML validation is in / can be in order) and the generated types, nested tables, varrays, etc need extra resources for maintenance. A lot of these “disadvantages” can be controled, managed via “xdb:annotations”. These “xdb:annotations” give you the advantage to disable unwanted behavior, for instance, regarding, index creation, DOM validation, etc.
An XMLType Table based on Object Relational storage will deliver, if applied / designed correctly, you with great performance in the case of select or update XML requirements or XML Schema evolution, thanks to big improvements from 11gR1 and onwards.
Within a Oracle database you are able to model you XML storage and structures based on, not only Object Relational XML structures, but also CLOB (Basicfile / Securefile) based, Binary XML and or a “hybrid” form combining the advantages of Object Relational and CLOB based storage. So in other words you have a whole range of functionality to address your XML requirements from native XML up to shredded storage principles.
😎
