Originally posted by paladin
As for the 2nd, it won't work with the query >>as specified<<.
...
I don't think a composite index will help if the only value in the where clause is the 100% NULL column. If you can make it work, I would be interested in seeing the example.
Code:
SQL> CREATE TABLE t AS SELECT ROWNUM id, ALL_OBJECTS.*
  2  FROM ALL_OBJECTS;

Table created.

SQL> UPDATE t SET id = NULL WHERE id =1;

1 row updated.

SQL> ALTER TABLE t MODIFY object_type NOT NULL;

Table altered.

SQL> CREATE INDEX t_idx1 ON t(id,object_type);

Index created.

SQL> ANALYZE TABLE t COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL indexed

Table analyzed.

SQL> set autotrace on explain
SQL> SELECT owner, object_name, object_type FROM t
  2  WHERE id IS NULL;

OWNER      OBJECT_NAME               OBJECT_TYPE
---------- ------------------------- -------------
SYS        /1001a851_ConstantDefImpl JAVA CLASS


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=47)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=
          47)

   2    1     INDEX (RANGE SCAN) OF 'T_IDX1' (NON-UNIQUE) (Cost=2 Card
          =1)
Your example was totaly nonadequate.

1. You neglected an important condition that slimdave mentioned: the other column in a composite index must be non-null. This must be known to the optimizer either by the definition of the collumn in the dictionary or by the predicate in a query.

2. You have update all of the rows to contain nulls in the indexed columns! So how dumb should the optimizer be to choose that index when it knows it will have to scan an entire index (plus visit all the rows in the table)?