Re: Why doesnt this query work?? Makes no sense????

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/03
Message-ID: <8jqlfb$tgk$1@nnrp1.deja.com>#1/1

In article <8jqe83$nt2$1_at_nnrp1.deja.com>,   ddf_dba_at_my-deja.com wrote:
> In article <8jbha5$8eo$1_at_nnrp1.deja.com>,
> Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> > In article <3959413e.5520978_at_news.mindspring.com>,
> > nitefrog_at_yahoo.com wrote:
> > > Why does this query not return any rows? I cant figure this one
 out?
> > > Also I want to note that the tablespaces are full or very close to
> > > being according to the storage manager. Will this cause the
 problem?
> > > Also this query takes for ever to run as well. Any help is
 greatly
> > > appreciated.
> > >
> > > select patientid
> > > from B
> > > where patientid not in (select patientid from A);
> > >

[snip]

> >
> > you have a NULL patientid in table A. When you say "where x not in

    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

[snip]

>
> I don't know which version of Oracle you are running, Tom, but I
 cannot
> get Oracle to exhibit the same behavior on 8.1.5:
>

you put the NULL row into the wrong table. Put a NULL row into tableA, not table B. Its the thing in the NOT IN ( .... ) that is the cause.

> SQL> create table a(patientid int, constraint a_pk primary
> key(patientid));

>

> Table created.

>

> SQL> create table b(patientid int, constraint b_uq unique(patientid));

>

> Table created.

>

> Using SQL*Loader I populated both tables a and b; the results of the
> data loads are shown below:

>
[snip]

>

> Now I add a NULL to table b:

>

thats the mistake.

[snip]

>
> The NULL value does not appear to affect the query speed or the
 results
> as you state. Again, maybe you're on a different release of Oracle.
>

No, i'm on 7.x, 8.0.x, 8.1.x..... they are all the same. Trust me on this one.

with a trivial amount of data, you'll see trivial runtimes. With lots of data -- you'll see a much LARGER runtime. Try this one:

ops$tkyte_at_8i> create table a as select -rownum patient_id from all_objects where rownum < 1000;

Table created.

Elapsed: 00:00:00.22
ops$tkyte_at_8i> create table b as select rownum patient_id from all_objects where rownum < 1000;

Table created.

Elapsed: 00:00:00.21
ops$tkyte_at_8i>
ops$tkyte_at_8i> alter table a add constraint a_unique unique(patient_id);

Table altered.

Elapsed: 00:00:00.07
ops$tkyte_at_8i> alter table b add constraint b_pk primary key(patient_id);

Table altered.

Elapsed: 00:00:00.07
ops$tkyte_at_8i>
ops$tkyte_at_8i> select count(*)
  2 from B
  3 where patient_id NOT IN ( select patient_id from A )   4 /

  COUNT(*)

---

       999

Elapsed: 00:00:01.89
ops$tkyte_at_8i>

Takes a bit of time -- just to show its not "cause it ain't cached", we'll do it all over again:

ops$tkyte_at_8i> select count(*)
  2 from B
  3 where patient_id NOT IN ( select patient_id from A )   4 /

  COUNT(*)

---

       999

Elapsed: 00:00:01.79

Not any faster really..... Now, lets try it in a method that allows for indexes:

ops$tkyte_at_8i>
ops$tkyte_at_8i> select count(*)
  2 from B
  3 where NOT EXISTS ( select null from a where a.patient_id = b.patient_id )
  4 /

  COUNT(*)

---

       999

Elapsed: 00:00:00.03
ops$tkyte_at_8i>

Thats much faster. Now, lets put that nasty NULL into the correct table:

ops$tkyte_at_8i> insert into a values ( NULL );

1 row created.

Elapsed: 00:00:00.00
ops$tkyte_at_8i>
ops$tkyte_at_8i> select count(*)
  2 from B
  3 where patient_id NOT IN ( select patient_id from A )   4 /

  COUNT(*)

---

         0

Elapsed: 00:00:01.65
ops$tkyte_at_8i>
ops$tkyte_at_8i> select count(*)
  2 from B
  3 where NOT EXISTS ( select null from a where a.patient_id = b.patient_id )
  4 /

  COUNT(*)

---

       999

Elapsed: 00:00:00.02
ops$tkyte_at_8i>

And the semantic differences between NOT IN and NOT EXISTS become apparent.

The advice I gave the first time (rewrite the query using "where patient_id is not null" in the subquery and using CBO to allow for the HASH anti-join or index probes, or using NOT EXISTS with RBO) is correct.

> --
> David Fitzjarrell
> Oracle Certified DBA

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Jul 03 2000 - 00:00:00 CDT