Improving sql speed

Galen Boyer · *nix forums Guru Wannabe Joined: 24 Mar 2005 Posts: 106

On 26 Feb 2005, upulbhradia@yahoo.com wrote:

Michael O'Shea · *nix forums beginner Joined: 01 May 2005 Posts: 22

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@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:

DA Morgan · *nix forums Guru Joined: 06 Mar 2005 Posts: 1042

U Radia wrote:

U Radia · *nix forums beginner Joined: 26 Feb 2005 Posts: 1

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

Google