Hello,

Thanks for the answer.
How can I optimize if I want to use a DML command in conjunction
with a subselect, e.g.


delete from test where Id in (
select min(t.Id) FROM test t
group by t.reference, t.key
having count(*) > 1
)



am Dienstag, 16. Dezember 2014 um 08:27 schrieben Sie:

> 15.12.2014 23:27, bjoern.reimer@... wrote:
>>
>> select min(t.Id) FROM test t
>> group by t.reference, t.key
>> having count(*) > 1
>>
>> costs 1000 Non indexed reads
>>
>> select * from test where Id in (
>> select min(t.Id) FROM test t
>> group by t.reference, t.key
>> having count(*) > 1
>> )
>>
>> costs 1001000 non indexed reads
>>
>> Why?

> IN predicate is always evaluated for the every row, because internally
> it's transformed into a correlated EXISTS equivalent:

> select * from test where exists (
> select * FROM test t
> group by t.reference, t.key

having count(*) >> 1 and min(t.Id) = ::id

> )


> Dmitry




> ------------------------------------

> ------------------------------------

> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

> Visit http://www.firebirdsql.org and click the Documentation item
> on the main (top) menu. Try FAQ and other links from the left-side menu there.

> Also search the knowledgebases at
> http://www.ibphoenix.com/resources/documents/

Björn