>Hello,
>
> thanks Set, makes my test unnecessary!
>
> But I'm thinking a little bit about my original problem.
>
> Would it make sense to add a tracker entry for optimization of
> subselects without reference to outer query?
>
> I think that they should get evaluated and transformed to something
> like a "or" connected list of simple compares.
>
> So
> delete from test where Id in (
> select min(t.Id) FROM test t
> group by t.reference, t.key
> having count(*) > 1
> )
>
> could be transformed by the engine to
>
> delete from test where Id = 5 or Id = 6 or Id = 89
>
> in case of
>
> select min(t.Id) FROM test t
> group by t.reference, t.key
> having count(*) > 1
>
> would return 5,6,89
>
> I've found several similar questions in my mail archive so I think
> I'm not the only one with such a problem.
>
> A common use case would be to remove duplicate entries for
> example to create a unique index.
>
> Any comments?
My guess is that it is too early to add this to Firebird. Sure, there are
situations where a non-correlated subselect would be benefitial, but how would
Firebird know when? In your case (since you're deleting from the same table as
you're selecting from), I agree that this could be simple. However, normally
the select would be against a different table and then I think things are not
as simple. Assuming we were talking about two different tables: I don't know
how Firebird translates a query like yours into EXISTS, but theoretically it
could be something like:
delete from DeleteFromTable t
where exists(select * from test t2
left join test t3
on t2.reference = t3.reference
and t2.key = t3.key
and t2.id > t3.id
where t.id = t2.id
and t3.id is null)
If DeleteFromTable was a smallish table, and test was large and sensible
indexes were in place, then my rewritten query would be quicker if your 'min
subquery' returned a lot of rows. On the other hand, if your subquery returned
few rows, then executing the subselect like you suggest would indeed be
quicker. If you add a WHERE clause to your subselect, it is even thinkable
that, say, WHERE MyField = 1 would be quicker using your solution, whereas
WHERE MyField = 2 would be quicker using mine. And the only way for Firebird to
know, would be to have histograms. Histograms was removed from things to be
implemented in version 3 (I think it was announced during the conference, at
least I'm certain that I've heard this), I hope they will appear in Firebird 4.
I would think that once histograms are in place, cases like yours are amongst
those that can be considered for optimization (although I don't know anything
about the source of Firebird).
You are definitely not the only one with this kind of problem, I would
appreciate it myself if it was possible for deletes and updates to use indexes
similar to what is possible with selects (e.g. allowing for JOIN). However, my
knowledge of SQL and Firebird is not good enough to formulate how to do this in
a sensible way. Also, I think that as users of Firebird it is possible to
rewrite our slow queries to more performant ones in most cases, so to me this
problem is not all too big.
I have never needed expression indexes myself, but have you looked into writing
one to see if this can make your query execute (considerably) faster?
Sorry for not agreeing with you in that this is a useful optimization just yet,
if you're lucky people knowing something about the source code of Firebird will
disagree with me.
Set
