Wednesday, June 13, 2012

XMLTYPE and DBMS_REDEFINITION

When Oracle first introduced the XMLType datatype (9.2.0.3), the default storage mechanism for it was a CLOB (aka BasicFile LOB). Note: I'm ignoring Object Relational storage since it is a table based format instead of a single column. With the release of 11.1.0.6, Oracle added a new storage type of SECUREFILE BINARY XML but left the default storage as CLOB. With the release of 11.2.0.2, Oracle changed the default storage to be SECUREFILE BINARY XML (What's New in Oracle XML DB?).

So what are the options for going from CLOB to BINARY XML storage? One is via DBMS_REDEFINITION. This may be the best option as it allows for the redefinition of the table without any system downtime. The downside to this option is that you need to have free space equal to or greater than the size of the new table as both the old and new will exist at the same time in the system. Assuming you can, here is what a simple example looks like (borrowed from myself from the OTN thread)

As you can see from the above DDL, the column is now a SECUREFILE BINARY XML. This also means that all the XML in that column has been converted to the more efficient (and smaller storage requirements) BINARY XML.

While researching this post, I came across someone mentioning the use of an ALTER TABLE statement to switch the storage formats. Here is what happens on 11.1.0.6 if we use the above table with a row in it.

About Me

I do what I do because I like it. I've had exposure to a lot of different products/technology over the time. I started with COBOL and flat files on a mainframe. I've used VSAM and DB2 there as well. Linux and Solaris have crossed my path several times. XML, XSLT and schemas have been friends for a while. SQL has seen me through MS Access, DB2 and Oracle. I've worked with 2 versions of DB2 and 3 of Oracle. There has been many other things as well, too small to mention.