4

We are currently evaluating possibilities to store generic data structures. We found that at least from a functional point of view Oracle XMLType is a good alternative to the good old BLOB. Because you can query and update single fields from the xml and also create indexes on XPath expressions.

We are a bit worried about the performance of XMLType. Especially the select performance in interesting. We have queries that select multiple data structures at once. These need to be fast.

Such a query looks something like this

SELECT DOC_VALUE.getClobval() AS XML_VALUE FROM XML_TABLE WHERE d.ID = IN ('1','2',...);

Our XML documents are 7 to 8 KB in size. We are on Oracle 11g and create the XML column with type XMLTYPE.

Do you have experience about the performance of selects on xml type columns? What overall experiences do you have with XMLTYPE? Is this a robust and fast Oracle feature? Or is it rather something immature and experimental?

Regards, Mathias

Improve this question

2 Answers 2

Reset to default
4

I have worked with Oracle Xmltype for the last 4 years, starting with Oracle 10.2.0.4 and now on Oracle 12c. I find it very reliable. We have used it not only for storage, but also XML manipulation. In that regard Oracle has chosen XQuery above XSLT.

The performance is dependent on the way you define the storage of these XMLs. You can store them as CLOB, as object-relational storage or (in 12c) as binary XML. The choice that you make depends on different parameters. CLOB keeps the white spaces in the XML. Object-relational storage is the smallest and binary XML (the default in 12c) is somewhere in between.

Regarding Query speed, I prefer binary xml, but all 3 solutions are very much workable in my experience. I didn't see any performance differencies between CLOB and Xmltype (stored as clob). When the XMLs are stored in a more compressed format, you could even gain speed.

Improve this answer
2
  • Thanks. That is very valueable information. What do you mean by "More compressed format"
    – mathiasdunkt
    Commented Oct 21, 2013 at 18:02
  • With the "More compressed format" I mean the 2 other storage options, because they take less space.
    – Roeland Van Heddegem
    Commented Oct 22, 2013 at 7:29
1

XMLType CLOB is deprecated from Oracle 12c and onwards. Regarding compression, AFAIK, currently this won't add to your performance speed.

XMLType Securefile Binary XML and/or XMLType Object Relational storage are the best way to go (XML schema or schemaless for Binary XML, XML schema based for XMLtype OR).

To get started have a look at the whitepapers at the XMLDB main page:

Best Practices for XQuery processing in Oracle Database 12c. June 2013 Best Practices for XML Storage in Oracle Database 12c

http://www.oracle.com/technetwork/database/database-technologies/xmldb/overview/index.html

XMLDB is used in various ways from small SOA environments up to TB environments with millions of XML documents. Small "how to's" can also be found here: www.xmldb.nl

XMLType Securefile Binary XML was introduced in database version 11.1 and can be deduplicated, secured or compressed. The syntax can be found here (http://www.liberidu.com/blog/2007/08/28/about-moving-deduplication-securefile-storage-and-encryption-of-binary-xmltype-tables/) or in the XMLDB Developers Guide for your database version.

Being close to "the front" I wouldn't use compression with XMLType Securefile alternatives before (at least) 11.2.0.3.0 (bug wise...)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.