Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL for finding rows in parent table without rows in child table
On 8 Apr 2005 10:43:01 -0700, "Davy" <daveellison2000_at_gmail.com>
wrote:
>My initial attempt using a subquery like:
>
>select distinct country_id from countries where country_id NOT IN
>(select distinct country_id from Cities)
>
>Won't work because my tables are huge (over 1 million rows each) and
>the query dosn't come close to finishing with so many records in the
>NOT IN statement.
As the element between parentheses constitute a set, and a set has per definition no duplicates, your DISTINCT is redundant. However the optimizer is not smart enough to remove it. Time to brush up your SQL.
-- Sybrand Bakker, Senior Oracle DBAReceived on Fri Apr 08 2005 - 14:09:42 CDT