anti-joins and performance
From: Fredrik Bertilsson <frebe73_at_gmail.com>
Date: Mon, 12 Nov 2012 21:39:22 -0800 (PST)
Message-ID: <f74485ea-047f-456f-b379-e2c49e9ad0a4_at_googlegroups.com>
Are there any way for an anti-join like below to perform better than O(N), N=number of rows in table1? In my case (Informix) the number of rows in both table1 and table2 are huge, and the expected result is only a few rows. Are there any chance that a database engine could return a result without actually having to make a full table scan in table1?
Date: Mon, 12 Nov 2012 21:39:22 -0800 (PST)
Message-ID: <f74485ea-047f-456f-b379-e2c49e9ad0a4_at_googlegroups.com>
Are there any way for an anti-join like below to perform better than O(N), N=number of rows in table1? In my case (Informix) the number of rows in both table1 and table2 are huge, and the expected result is only a few rows. Are there any chance that a database engine could return a result without actually having to make a full table scan in table1?
select *
from table1 t1
where not exists (select * from table2 t2 where t2.key=t1.key)
Received on Tue Nov 13 2012 - 06:39:22 CET