Re: A Question regarding null columns.

One possible reason is NULL can not be indexed with a normal index. It would need a Bitmap index. (Unless someting has changed with newer Oracle versions)

0 <> NULL
heck.. NULL <> NULL keep that in mind

to test you can make a procedure..
if (NULL=NULL) then ... this will be be done because NULL <> NULL.

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:<ZVZT8.588$i5.5345_at_news.indigo.ie>...
> Consider :
>
> (Oracle 8173)
> Create table eraseme as select object_id from all_objects;
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
> JServer Release 8.1.7.3.0 - Production
>
> SQL> desc eraseme;
> Name Null? Type
> ----------------------------------------- -------- ----------
>
> OBJECT_ID NOT NULL NUMBER
>
>
> SQL> explain plan for select count (*) from eraseme where object_id is null;
>
> Explained.
>
> SQL> @ ?/rdbms/admin/utlxplp.sql
>
> Plan Table
> ----------------------------------------------------------------------------
> ---
> ----------------------------
> | Operation | Name | Rows | Bytes| Cost | TQ
> |IN-OUT|
> PQ Distrib | Pstart| Pstop |
> ----------------------------------------------------------------------------
> ---
> ----------------------------
> | SELECT STATEMENT | | | | | |
> |
> | | |
> | SORT AGGREGATE | | | | | |
> |
> | | |
> | TABLE ACCESS FULL |ERASEME | | | | |
> |
> | | |
> ----------------------------------------------------------------------------
> ---
> ----------------------------
>
> 6 rows selected.
>
>
> Without wishing to cause major fuss my question would be -
>
> since object_id is known to be not null from the data dictionary why does
> the optimizer ask for an FTS rather than immediate 0 ?
>
> or is it a 'fiddled' FTS ? Checking the waits (there aren't many but there
> are a few consistent reads )
>
> Just part of an issue I am trying to solve with distributed tables (the
> above is self-contained on one instance though)
>
> TIA
>
> T
Received on Mon Jul 01 2002 - 15:27:42 CDT