Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How can I make this SQL stmt more efficient?
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