Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL for finding rows in parent table without rows in child table

Re: PL/SQL for finding rows in parent table without rows in child table

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 08 Apr 2005 21:09:42 +0200
Message-ID: <lkld51tcavujccsv4p8100tsg8fpfrb72b@4ax.com>


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 DBA
Received on Fri Apr 08 2005 - 14:09:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US