A Question regarding null columns.

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 1 Jul 2002 15:45:13 +0100
Message-ID: <ZVZT8.588$i5.5345@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 - 09:45:13 CDT