Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How can I make this SQL stmt more efficient?

Re: How can I make this SQL stmt more efficient?

From: David Grzebien <dgrzebie_at_columbus.rr.com>
Date: Sat, 11 Nov 2000 03:27:51 GMT
Message-ID: <3A0CBE13.492C3E21@columbus.rr.com>

Based on the UPDATE statement, every row in ALODETAIL where DIM_7 = '1' will require a read back at ALODETAIL to see if if DIM_7 = '1' and VOUCHER_TYPE is 'CC','CD', or 'CE'. The same record will have to read ALOLOAN where DIM_3 = '3'. This is based on the NOT EXISTS logic.

You may want to try the NOT IN clause, depending on the number of rows returned by the sub-queries. The NOT IN clauses will basically execute the subquery first and the use the results of the subquery to compare against the original UPDATE.

Dave Grzebien
dgrzebien_at_etci.net

mitch23_at_hotmail.com wrote:

> I have the following SQL statement:
>
> UPDATE ALODETAIL A SET DIM_7 = '-1' WHERE DIM_7 = '1'
> AND NOT EXISTS
> (SELECT LOAN FROM ALODETAIL WHERE DIM_7= '1' AND VOUCHER_TYPE IN
> ('CC','CD','CE') AND LOAN = A.LOAN)
> AND NOT EXISTS
> (SELECT LOAN FROM ALOLOAN WHERE DIM_3 = '3' AND LOAN = A.LOAN)
>
> It executes quickly if there only a few 100 records in ALODETAIL with a
> DIM_7 = '1', but takes forever for larger recordsets.
>
> Can someone help me fine tune it? I'm using ORcale 8.0.5 if that
> matters.
>
> Thanks
>
> Mitch Abaza
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Fri Nov 10 2000 - 21:27:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US