|
Hello community, 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 asked Oct 18 '13 at 12:18 
zaddo |
|
Awesome question. Really.
Here I'd instantly agree. The more the database knows about the structure of your data, the better it is on the long run because it allows you to easily access data fragments inside the document.
No, unfortunately. But I know that you can index data from the XML-Docs themselves (technically a function based index) and I've no reason to believe this isn't mature. Considering that the XML stuff is not an Oracle specific extension, but part of the ISO SQL Standard I've also no reason to believe the API could be immature. On the other hand, I've been against the XML extension in the XML Standard in the past. I'm sure I've said that the "XML extensions should never have been added to the standard." The reason behind this is that I believe the parsing&building of the XML structure should be the responsibility of the presentation layer or the View of MVC whatever terminology you prefer. In respect with commercial products like the Oracle database—being a particularly expensive one—I also bring up that the parsing&build of XML in the DB runs on the cores you have to pay Oracle license fees for. If you do it in the application, you'll usually not have the pay (that much) for parsing&building XMLs. I still think the ideal solution is to use tables & columns to store that data and do the XML stuff outside the DB. If that is not feasible, XML in the DB is probably better than BLOBs/CLOBs. answered Oct 18 '13 at 13:43 
Markus Winand ♦♦ |
Brendan Furey contributed this link via twitter (@BrenPatF): http://aprogrammerwrites.eu/?p=783