Re: XMLType performance

 > The question is if I have to store XML vs
> relational when/if should I choose one over the other.

Well, at some stage you have to consider the underlying storage mechanism.

XML storage in a CLOB is good for speed of initial insert, but sucks (as in text queries, which are still quite good) for queries within the XML structure, and absolutely sucks if you want to strip tags. Great if 100% document fidelity is important however.

XML storage against an OR database (where the XML doc is shredded into components) is great for all sorts of queries as indexes can be built for each element, the tag removal reduces disk space, but does require a schema and some sort of processing cost when doing DML (but that can be mitigated as well). So I would potentially recommend this for the tax example you cited.

XML storage in the 'binary' format is a good halfway house between the two - not all the speed of the queries against stripped XML, but faster than text searches on CLOBs, does not require a schema, so more easy to use for transient XML data, such as the randomish messages in SOA environments. Of course, persisting such messages to a database is something that would need to be well-designed to remove any potential performance bottlenecks. You would probably also want to wrap some level of message and queue semantics around this, using something like IBM's MQSeries (which does have the capability to persist in a db, not sure if it uses the DB2 XML types), or Oracle Advanced Queuing, which will use the Oracle XML types

Of course, you also have to look at what happens if the XML document needs to evolve - easier to do if an XML schema defines the old and new structure as it's reasonable easy to automate transformations between the old and new OR structures, not an issue in clob storage (although queries could be an issue), and potentially an issue with any specific implementation of XML binary storage.

Horses for courses - you pays yours monies, and you take yours picks. Received on Fri Apr 13 2007 - 20:34:55 CDT