Home » Developer & Programmer » JDeveloper, Java & XML » STRUCTURED OR UNSTRUCTURED STORAGE IN XML & PERFORMANCE
STRUCTURED OR UNSTRUCTURED STORAGE IN XML & PERFORMANCE [message #265206] Wed, 05 September 2007 14:28 Go to next message
Messages: 1
Registered: September 2007
Junior Member
Hi All,
I am looking into workability of querying XMLDB in Oracle - in particular I am wondering about performance issues associated with querying XML stored in a table which has data stored in XML type column in a table.
Xml stored in XMLType column in this table has the a series of tags(see in toad, schema browser)
in the format:
<subParent1 name = product id="1" </subParent1>
<subParent2 name = Category value = "Fixedincome" </subParent2>
There is also xls_data field which is of type BLOB ( I assume this is a metadata field).
There are a number of join keys in this table, which enable join to relational tables.
There is one schema for one product type i.e within the table, there are several schemas and hence access paths - varying by product type.
So, as you go down the table, the XML schema(I mean access path created by elements) change from product type to product type.
Is this structured or unstructued storage and how is the performance affected in this kind of storage? Is this hybrid storage? Is Oracle "schema aware" in this case and is it able to convert the XML into tables and relational sql for faster access.
Re: STRUCTURED OR UNSTRUCTURED STORAGE IN XML & PERFORMANCE [message #267706 is a reply to message #265206] Fri, 14 September 2007 06:02 Go to previous message
Messages: 5
Registered: March 2006
Junior Member
That looks like a hybrid to me. Unstructured storage is CLOBs structured storage is where you register the XSDs with Oracle and let it generate the tables and types for you which the XML is shredded into.
Performance wise, if you're mostly inserting data then unstructured is faster as the XML is not validated against an XSD and doesn't have to be shredded. Obviously querying a CLOB isn't possible so for reporting purposes structured is faster and easier to use, you don't have to parse the XML string.
When I've done this before, I've inserted the XML as a CLOB and used a job to take the CLOBs and shred them into the structured schemas so you get the best of both worlds. Of course this means some of your data is replicated, breaking 3rd Normal Form but it's worth it to get transactional performance and easy reporting / querying.
Previous Topic: Password validation
Next Topic: Issue with timestamp precision
Goto Forum:

Current Time: Fri Jul 21 00:09:54 CDT 2017

Total time taken to generate the page: 0.07973 seconds