Category: 11gR1, 11gR2
October 16

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.

Read the postHOWTO: Partition Binary XML, XMLType Storage
September 30

Oracle Open World 2009 – XMLDB Sessions and Presentations

Its time to enlist. It really is. At least regarding the XMLDB related sessions and presentations during Oracle Open World 2009. As far as I have seen today, the hands-on workshop has almost no seats left and more than half already enlisted for my XMLDB New Features and XMLDB Performance related presentation.

Oracle Open World doesn’t have much XMLDB related sessions, until now from the 1800+ sessions, I only counted a dozen or so. If you want to have a peak of the ones I found, have a look at the following Oracle OTN XMLDB Forum thread.

I enlisted for most, as long as they didn’t interfere with my presentation schedule, if not only that I am very anxious about Mark Drake’s presentations about Google API mashups etc combined with the XMLDB functionality in the database and the, probably extended version of how to build an APEX content management system based on the versioning and filler capabilities of the XMLDB Repository. During OOW 2008, Carl Backstrom, helped and co-presented this APEX / XMLDB joined venture and, alas, this demo app. of the XFILES light weight XMLDB CMS based on APEX, never had (at least yet AFAIK) the chance to be a hit on the apex.oracle.com demogrounds or the OTN XMLDB main page after the source code had been cleaned-up.

For those who might enjoy a photo’s from that presentation with Mark and Carl during Oracle Open World 2008:

Read the postOracle Open World 2009 – XMLDB Sessions and Presentations
September 9

Oracle 11gR2 – XML Data Partitioning

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.

Read the postOracle 11gR2 – XML Data Partitioning