| Subject | Re: Delete with join and delete from multiple tables |
|---|---|
| Author | Adam |
| Post date | 2007-09-13T00:41:39Z |
--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
I think he is referring to the optimiser always starting with the top
table.
For example:
delete from a
where a.id in
(
select b.id
from b
join c on (b.cid = c.id)
)
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.
Adam
>Hi Helen,
> 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?
I think he is referring to the optimiser always starting with the top
table.
For example:
delete from a
where a.id in
(
select b.id
from b
join c on (b.cid = c.id)
)
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.
Adam