Re: A Question regarding null columns.

On Mon, 1 Jul 2002 15:45:13 +0100, "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote:

>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
>

No sign of any ANALYZE TABLE so rule-based optimizer. Refer to the IS NULL clause in the select statement. In RBO IS NULL will *NEVER* use an index.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Jul 01 2002 - 12:24:22 CDT