Re: Query brings system dowh, needs help

gmisrod_at_access.digex.net (gmisassoc) wrote:
>Path: csus.edu!news.starnet.net!wupost!howland.reston.ans.net!news1.digex.net!digex.net!not-for-mail
>From: gmisrod_at_access.digex.net (gmisassoc)
>Newsgroups: comp.databases.oracle
>Subject: Query brings system dowh, needs help
>Date: 1 Mar 1995 07:21:35 -0500
>Organization: Express Access Online Communications USA: 800-969-9090
>Lines: 15
>Message-ID: <3j1osf$95b_at_access1.digex.net>
>NNTP-Posting-Host: access1.digex.net
>
>
>I have two tables and I am running the following query:
>
>SELECT id FROM table1 WHERE id NOT IN
>(SELECT id FROM table2)
>
>Both tables are about 20,000 records each. This query is
>bringing a Sequent to its knees.

Not unusual, even assuming you have the right indexes. The manuals should warn you to not do this.

>
>What I want are all the ids that are in table1 but are not in table2.
>
>Any help is appreciated.
>
>Please respond to
>

A syntactically untested possible solution for adhoc SQL*Plus queries:

spool yourtest.lst

col "t2_id_noprint" noprint ;

select

       t1.id,
       t2.id "t2_id_noprint"
  from
       table1 t1,
       table2 t2
 where
       t1.id = t2.id (+)
   and
       t2.id is null

/
l

spool off

Any corrections or other feedback is appreciated,

Eric D. Pierce
Data Janitor
CSUS Received on Wed Mar 01 1995 - 21:44:55 CET