Re: Improving sql speed

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 26 Feb 2005 08:20:19 -0800
Message-ID: <1109434819.029609.298660@z14g2000cwz.googlegroups.com>

Upul, there is not much to go on from what you have posted. To help you out in any level of detail you will have to post a snippet of your XML schema, Oracle version, etc.

Anyway, the query as you have written it is not scalable and I am not surprised you are having performance problems. I also have the distinct impression you are storing the information in a datatype of VARCHAR2 or CLOB. For best results you will find the XMLTYPE datatype and XPATH expressions appropriate here. The relevant Oracle documentation search term is XMLDB.

In addition to missing the column name in your SQL snippet posting, note too that what you have written is bugged. Wildcard searching the XML for '...%<approved' does not tell you whether the tag present or not in the XML! What if, for example, the XML contained "<approved/>" or "<approved></approved>". Accordingly to the XML grammar, both are still missing "approved" content yet would be included in the total returned by the execution of your SQL statement.

As an interim answer, I feel you are possibly asking for a query of this nature.

SELECT COUNT(*)
 FROM debtor3y
  WHERE NOT EXTRACTVALUE(put table column name in here,'/audit/x1/a9004[count(*)=1]/authorID') IS NULL;

Enhance/refine the XPATH expression as necessary. This type of construct can also be used to retrieve, for example, the author ID's in your XML. You don't want to be using SUBSTR.

Eg,

SELECT EXTRACTVALUE(put table column name in here,'/audit/x1/a9004/authorID')
 FROM debtor3y;

Regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429

U Radia wrote:
> I audit changes in columns with XML. Any changes are approved but I
> need to find how many changes are not.
>
> This query is slow. Can I speed it?
>
> select count(*) from debtors3Y where like
> '<audit><x1><a9004><authorID%' and not like
> '<audit><x1><a9004><authorID%<approved%';
>
> Upul
Received on Sat Feb 26 2005 - 10:20:19 CST