Now that sounds all easy then, AnaCDent. I am sure explain plan will help as well.
AnaCDent <anacedent_at_hotmail.com> wrote in message news:<%_Hcc.39$Wo6.28_at_fed1read03>...
> arjan wrote:
> > Hi there,
> >
> > I have a delete sql that runs too slow (around 15 mins). I would like
> > to improve it.
> >
> > It looks like this:
> >
> > delete from table_a a
> > where exists (select *
> > from table_b b
> > where b.columnX = a.columnX
> > and b.columnY = a.columnY)
> >
> > There are two indexes. IndexA on table_a(columnX,columnY) and IndexB
> > on table_b(columnX,columnY).
> >
> > Looking at the statement in explain plan, it shows that it's doing
> > full scans on both tables. It's not picking up an index hint like
> > this:
> >
> > delete /*+ INDEX(a IndexA) */ from table_a a
> > where exists (select *
> > from table_b b
> > where b.columnX = a.columnX
> > and b.columnY = a.columnY)
> >
>
> Stop throwing darts in the dark.
> Get facts before proceeding.
> Otherwise all you are doing is....
>
> Ready, Fire, Aim!
>
> ALTER SESSION SET SQL_TRACE=TRUE
> DELETE....
>
> then go run TKPROF & actually see where Oracle is spending all the time.
Received on Wed Apr 07 2004 - 03:44:34 CDT
