Re: Query brings system dowh, needs help

In article <3j2mgq$nsa_at_news.csus.edu>, PierceED_at_CSUS.edu writes:

> >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.

.
.
> 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
>

Try this

select id FROM table1
minus
SELECT id FROM table2;

It should do what you require considering that all that you want is the ID.
Only requires two full table scan.

-- 
 _____       ________ / ___ |Tony Jambu, Database Consultant
  /_  _        /_ __ /      |Wizard Consulting,Aust (ACN 065934778)
 /(_)/ )(_/ \_/(///(/_)/_(  |CIS: 100250.2003_at_compuserve.com FAX: +61-3-2536173
 \_______/                  |Email:TJambu_at_wizard.com.au PHONE: +61-3-2536385