Re: Oracle 9i2 and XML performance

Olivier Guyot wrote:

> Hello,
>
> I am working on a project using XMLType datatype.
>
> We are storing few but very large XMLType documents. As a beginning, I
> thought using Oracle Text for speeding up queries, but it does not
> work because we are not searching data through XML but only extracting
> data for a single row (found with an Integer ID). As I have
> understood, Oracle Text allow You to search through multiples rows.
>
> So my application is pretty slow. I have installed Statpack for
> monitoring the database ...
>
> The problem is : the queries are takin huge CPU's and few consistent
> gets. I think the manipulations of XML Data made by Oracle are huge
> CPU consummers.
>
> example of queries we have written :
>
> SELECT extract(value(t),'./data @status').getstringval from patient
> pat, table(xmlsequence(extract(pat.pat_data,'//data'))) t
> where pat.pat_id=:b1
>
> Our XMLData we are storing are from 300Kb up to 1Mb large.
>
> Thx per advance,
>
> Olivier
>
> Has somebody an idea on howto speed up

I would advise against storing XML as XML. Store it in relational or Object Relational tables and reconstitute if as XML on-the-fly if need be.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)