Re: Long running SQL Problem? [stupid alternative to NOT IN]

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Wed, 27 Mar 2002 15:03:39 -0800
Message-ID: <F001.00435667.20020327150339@fatcity.com>

ORACLE-L Digest -- Volume 2002, Number 086
> From: Denham Eva <EvaD_at_TFMC.co.za>
> Date: Wed, 27 Mar 2002 10:58:23 +0200
> Subject: Long running SQL Problem?

...

> Is there anyone who can give me a solution to this problem.

get faster/more RAM, CPU, hard drives, etc?

> It is a sql that runs forever and I eventually have to kill it, both tables
> are large 500000 + rows.
> Is there perhaps a quicker more effecient way of doing this.
>
>
> SELECT col1,col2
> FROM Table_1
> WHERE (col1,col2) NOT IN (SELECT col3,col4 FROM Table_2);

these are indexed?

probably no faster, but will probably barf out the initial chunks of output as it goes along, and thus seem slightly less boring to anyone observing the output during runtime:

SELECT

       col1,
       col2

/* debug only:
,

       col3,
       col4

*/
  FROM

       Table_1 t1,
       Table_2 t2
WHERE 
       t1.col1 = t2.col3 (+)
   and 
       t1.col2 = t2.col4 (+)
   and 
       (
            t2.col3 is null
        and 
            t2.col4 is null
       );

btw, there is a similar alternative using "exists", but I haven't found it to be any faster.

regards,
ep

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Eric D. Pierce
  INET: PierceED_at_csus.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Mar 27 2002 - 17:03:39 CST