Re: slow delete sql

Use can also use delete on an inline view if the view is key-preserved. But it should be if X and Y are primary keys in both tables.

delete from (select a.columnX, a.columnY from table_a a inner join table_b b on (a.columnX = b.columnX and a.columnY = b.columnY))

/Kristian

"arjan" <adalebout_at_hutchison.com.au> wrote in message news:55cbd77.0404061946.5eeef8d4_at_posting.google.com...
> Hey thanks Mariusz. It does run much faster using this sql.
>
> columnX+columnY aren't the primary keys, but instead of a filter on
> both tables it does now a hash join. Really great. I would never
> thought of doing it this way. Thanks, mate!
>
> "MariuszR" <rustek2_at_poczta.onet.pl> wrote in message
news:<c4ujdr$1q19$1_at_news2.ipartners.pl>...
> > > 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).
> >
> >
> > I suppose that columnX+columnY makes the primary key in both tables.
> > Try this:
> >
> > delete from table_a where (columnX,columnY) in (
> > select a.columnX, a.columnY from table_a a, table_b b where
a.columnX =
> > b.columnX and a.columnY = b.columnY
> > )
> >
> > You should have full scan on one table only.
> >
> > Greetings
> > Mariusz
Received on Wed Apr 07 2004 - 08:44:23 CDT