Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
Thanks. That is very valueable information. What do you mean by "More compressed format" –  mathiasdunkt Oct 21 '13 at 18:02
    
With the "More compressed format" I mean the 2 other storage options, because they take less space. –  rvheddeg Oct 22 '13 at 7:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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