XMLData in oracle

[Sam Semwal]

Hi,
I am trying to use xmldata field in oracle. I am using this data in J2EE application. This is really affecting the performance seriously. Is there a way to tune the database or any option which stops oracle not to index xmldata field?

One more question how can we use self describing data? Is XML the only one which is self describing.


Many thanks for your help

[Paul Clapham]

How can you tune Oracle for better performance with XMLDATA fields? Let's try the Oracle forum and see if anybody there can answer that.

[Sam Semwal]

Hi,
I have got the answer :
CLOB-based XML storage stores the entire document byte for byte. Object-relational storage, on the other hand, enjoys some efficiency in the area of storage size and access to scalar values of the document. Consider the element and attribute names in an XML document. Consider all the markup! In a CLOB, the element name usually appears at least twice, in an open tag and a close tag. Typically, element and attribute names appear much more than twice, because elements and attributes are commonly repeated in XML documents. With object-relational storage, the element and attribute names become the object attribute name, and Oracle's data dictionary stores that name only once. The object-relational approach means no more storage of brackets or of excessive quotation marks. All these bytes are saved, because only the scalar values require storage in the object tables.
Data access is where object-relational storage shines. When you query an XML column or table, the database must decide how it will retrieve the data from the physical data blocks. To query against CLOB-based XML storage (unless queried values exist in indexes), entire documents must be read into memory and a document object model (DOM) must be traversed to find the values to include in a result set. Object-relational storage, however, uses a process called query rewrite, which lets the Oracle optimizer translate an XPath expression into an object-relational query that accesses individual rows of the object tables and lets it read only the attributes necessary for the query. Therefore, object-relational storage tends to enable much better performance in data manipulation language (DML)-heavy applications (when the Oracle optimizer can use query rewrite).

SO I tried and tested and conveterted my xmltype db which was in the form of clob to xmltype db. The response was 4 times faster in object-relational method.