Re: IS NULL against = NULL

From: David Fitzjarrell <oratune_at_aol.com>
Date: Thu, 09 Nov 2000 14:13:13 GMT
Message-ID: <8uebdj$ori$1@nnrp1.deja.com>

In our last gripping episode SpaceHopper <spacehopper_at_attglobal.net> wrote:
> Hello,
>
> When I read further into the Oracle 8 SQL Reference
> manuals it explained it better to me.
>
> However why does it not use the index when using IS NULL ?
> I know this is not strictly an equality test, but you would have
> thought that it would still be able to utialize a unique index when
> present ?
>
> Thanks for your help
> Ian
>
> sybrandb_at_my-deja.com wrote:
>
> > In article <3A08FFE7.5EB26F5C_at_attglobal.net>,
> > Ian Meikle <Ian_Meikle_at_attglobal.net> wrote:
> > > Hello,
> > >
> > > From my short time doing SQL I did not know it was possable to
 have
> > > an operator equal (=) to NULL, i.e.
> > >
> > > WHERE SOME_COLUMN = NULL
> > >
> > > I have always written it
> > >
> > > WHERE SOME_COLUMN IS NULL
> > >
> > > However, I ran the following SQL
> > >
> > > SELECT COUNT(*) FROM SOME_TABLE WHERE SOME_COLUMN IS NULL
> > >
> > > and got the following explain plan:
> > >
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=382 Card=1 Bytes=34)
> > > SORT (AGGREGATE)
> > > TABLE ACCESS (FULL) OF SOME_TABLE (Cost=382 Card=1 Bytes=34)
> > >
> > > I found this kind of strange since I have a unique index on
 SOME_COLUMN
> > >
> > > Then I ran the SQL as:
> > >
> > > SELECT COUNT(*) FROM SOME_TABLE WHERE SOME_COLUMN = NULL
> > >
> > > I got the following explain plan:
> > >
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=34)
> > > SORT (AGGREGATE)
> > > INDEX (UNIQUE SCAN) OF I_SOME_COLUMN (UNIQUE) (Cost=1 Card=1
> > > Bytes=34)
> > >
> > > and the query ran about 100 times faster (obviously)
> > >
> > > Can anyone explain this ??
> > >
> > > Cheers
> > > Ian
> > >
> > >
> >
> > Using = NULL won't provide correct results as NULL is nothing and
 can't
> > be compared to anything else. You will not be able to use the
 equality
> > operator with NULL. It's not proper sql either.
> > You are getting the different plans because you don't use the
 equality
> > operator in 1 and do use the equality operator 2. However your
 second
> > statement is incorrect and you will be bound to sue a full table
 scan.
> >
> > Hth,
> >
> > --
> > Sybrand Bakker, Oracle DBA
> >
> > All standard disclaimers apply
> > --------------------------------------------------------------------

---

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

A unique index contains no NULL values, therefore there is nothing to search for. Oracle knows this and forgoes using the index because it would be useless.

--
David Fitzjarrell
Oracle Certified DBA


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

Received on Thu Nov 09 2000 - 08:13:13 CST