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

mitch23_at_hotmail.com writes:

> 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.

It's going to be impossible for people to really help you unless you provide more information. In particular what columns have indexes, on which columns could you create indexes, and what kinds of cardinality do these columns have?

If alodetail.loan is a high cardinality indexed field (possibly a primary key for the master loan table?) then you would hope the two NOT EXISTS clauses would just use it to look up a small set of records relevant to this loan. How many detail records would you expect to find for each loan? If there's already an index what does the explain plan look like now, is it doing a range scan on loan?

-- 
greg