Re: IS NULL against = NULL

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.
  Received on Thu Nov 09 2000 - 05:47:10 CST