Re: ORACLE-L Digest -- Volume 2002, Number 023

From: Eric D. Pierce <PierceED_at_csus.edu>
Date: Wed, 23 Jan 2002 17:25:50 -0800
Message-ID: <F001.003F95E0.20020123171521@fatcity.com>

Ethan,

Thanks for the alternative code.

I got the exact same time either way.

table "a" = 7000 records, table "b" = 30,000 records.

old netware/oracle7 server.

  1 select
  2 count( m.student_ssn )
  3 from

  4         student_master m,
  5         SIS_CSUS_ALL_FALL_2001_eos1 x
  6   where
  7         m.student_ssn = x.stu_id (+)
  8     and
  9*        x.stu_id is null

SQL> / COUNT(M.STUDENT_SSN)

(35 seconds)

  1 select
  2 count( m.student_ssn )
  3 from
  4 student_master m
  5 where

  6         not exists
  7       (
  8         select
  9                null
 10           from
 11                SIS_CSUS_ALL_FALL_2001_eos1 x
 12          where
 13                m.student_ssn = x.stu_id
 14*      )

SQL> / COUNT(M.STUDENT_SSN)

(35 seconds)

best regards,
ep

On 23 Jan 2002 at 1:05, Oracle RDBMS Community Forum wrote:

ORACLE-L Digest -- Volume 2002, Number 023
> ------------------------------
>
> From: "Post, Ethan" <Ethan.Post_at_ps.net>
> Date: Tue, 22 Jan 2002 17:17:23 -0600
> Subject: RE: Sql query
>
> I believe this will work also and may be faster...
>
> select c1 from t1 a where not exists (select null
> from t2 b where a.c1 = b.c1);
>
>

-- 
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 Jan 23 2002 - 19:25:50 CST