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

In our last gripping episode 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.
>

I would run explain plan on your statement and determine what Oracle is doing with it. Verify that you're using indexes where defined. Outside of that you're using NOT EXISTS, which is a more efficient algorithm than NOT IN. As long as indexes are being used for your subqueries I cannot see any way to fine tune this statement (although I am reasonably certain that someone will prove me wrong). Remember that update statements will generate rollback information, and the larger the affected data set is the more rollback that is generated.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.