Hi,
I am working on an application where we use XML-DB to store the data.
Data comes in millions of records and each record has 150 columns of which we create XML file (i.e XML file with 150 tags which contain data) and we store it in a table CYK_doc. Now the table CYK_DOC store a primary key (ID) and XMLfile (CYK_DOC as XMLType). Later we unbox the xmlfile to different tables depending upon the tagnames and nodes of xmlfile..
We are using functions like XMLSequence, Extractvalue and Extract to retrive the data stored in different tags of XMLFile of the table CYK_DOC table.. the package we wrote takes around 1.5 seconds for one record but when we run the same package for 50k records, it took around 8-9 hours to complete the unboxing.
I was thinking if there is a way to create indexes on such XMLType columns( which after surfing for last 2 days, I realised that, is not possible).
There should be a way to enhance the performance of what is being done above.. In a way , i want to reduce the time taken there from 1.5 seconds to 0.5 seconds for each record.. I suppose there should be way to do it..
The way we build XML file is( somewhat similar to) as below..
<?xml version="1.0"?>
<catalog>
<row id="bk101">
<author>Gambardella, Matthew</author>
<title>XML Developer's Guide</title>
<genre>Computer</genre>
<price>44.95</price>
<publish_date>2000-10-01</publish_date>
</row>
<row id="bk102">
<author>Ralls, Kim</author>
<title>Midnight Rain</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-12-16</publish_date>
</row>
<row id="bk103">
<author>Corets, Eva</author>
<title>Maeve Ascendant</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2000-11-17</publish_date>
</row>
<row id="bk104">
<author>Corets, Eva</author>
<title>Oberon's Legacy</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-03-10</publish_date>
</row>
<row id="bk105">
<author>Corets, Eva</author>
<title>The Sundered Grail</title>
<genre>Fantasy</genre>
<price>5.95</price>
<publish_date>2001-09-10</publish_date>
</row>
<row id="bk106">
<author>Randall, Cynthia</author>
<title>Lover Birds</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-09-02</publish_date>
</row>
<row id="bk107">
<author>Thurman, Paula</author>
<title>Splish Splash</title>
<genre>Romance</genre>
<price>4.95</price>
<publish_date>2000-11-02</publish_date>
</row>
</catalog>
Please guide me in anyway possible.. I am very bad at performance tuning actually...
Thanks
Mahesh