| STRUCTURED OR UNSTRUCTURED STORAGE IN XML & PERFORMANCE [message #265206] |
Wed, 05 September 2007 14:28  |
ajq_s
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:
<Parent1>
<subParent1 name = product id="1" </subParent1>
<subParent2 name = Category value = "Fixedincome" </subParent2>
</Parent1>
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.
thanks,
ajq
|
|
|
|
| Re: STRUCTURED OR UNSTRUCTURED STORAGE IN XML & PERFORMANCE [message #267706 is a reply to message #265206] |
Fri, 14 September 2007 06:02  |
oliversalmon
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.
Oliver
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
