In article <8jqlfb$tgk$1_at_nnrp1.deja.com>,
Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> 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.
With a primary key on table A a NULL value CANNOT be inserted.
>
> > 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.
>
No, it isn't a mistake, since a NULL is not allowed in table A (primary
key) -- please read the 'create table' statements again. However,
reversing the data sets DID produce the results you mentioned.
> [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:
>
Again, the NULL was placed in the CORRECT table, the only table that
would accept null values.
> 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.
>
--
David Fitzjarrell
Oracle Certified DBA
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Jul 03 2000 - 00:00:00 CDT
