Subject Re: Delete with join and delete from multiple tables
Author Adam
--- In, Helen Borrie <helebor@...> wrote:
> At 02:09 AM 13/09/2007, you wrote:
> >Hi
> >
> >
> >
> >Thanks a lot for all the good and prompt responses. I'd say this is
> >an unfortunate limitation
> Which limitation are you referring to?

Hi Helen,

I think he is referring to the optimiser always starting with the top

For example:

delete from a
where in
from b
join c on (b.cid =

If there are a *lot* of records in 'a', and the sub query join only
returns a *few* records (and does not rely on knowing the current
value of 'a'), a faster plan would run the subquery and issue a delete
for each record it discovers.

Of course if the tables are turned and you have a huge recordset in
the subquery and a relatively small 'a', the current plan of checking
an existence for each 'a' record is faster.

I also hope one day the optimiser will be able to know which plan to
use and always get it right. But I am also a realist and know that is
a lot more complex than it sounds.