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
Best 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.