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

I'd also tried to use cost-based optimizer here, it's more efficient with antijoins. Just put /*+ choose */ after update keyword. Of course, use of explain plan and trace/tkprof would give you a better insight into what's going on.

In article <3A0CBE13.492C3E21_at_columbus.rr.com>,   David Grzebien <dgrzebie_at_columbus.rr.com> wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Nov 12 2000 - 22:40:21 CST