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, [email protected] 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 ------------------------------------ ------------------------------------ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 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/ ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ------------------------------------ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
