Originally posted by slimdave

Secondly, is the tran_datetime column nullable? Without a NOT NULL constraint it could not be used to help in this query unless you included the predicate "tran_datetime IS NOT NULL".
Wrong. How do you know if the optimizer will not use it. Look at what I did:

Code:
SQL> desc file_size

 Name                            Null?    Type
 ------------------------------- -------- ----------------------
 SID                             NOT NULL NUMBER
 PVM                             NOT NULL DATE
 DB_NAME                                  VARCHAR2(15)
 FILE_NAME                                VARCHAR2(100)
 SIZE_MB                                  VARCHAR2(20)
 USED_MB                                  VARCHAR2(20)
 FREE_MB                                  VARCHAR2(20)
 HWM_MB                                   VARCHAR2(20)

-- Note DB_NAME is nullable

SQL> CREATE  INDEX ndx_db_name
  2   ON file_size
  3    ( db_name );

Index created.

SQL> analyze table file_size compute statistics;

Table analyzed.

SQL> set autotrace on

SQL> select * from file_size where db_name = 'SS02_V60';

       SID PVM             DB_NAME         FILE_NAME                                     SIZE_MB              USED_MB              FREE_MB              HWM_MB
---------- --------------- --------------- --------------------------------------------- -------------------- --------
         1 17-DEC-02       SS02_V60        /u03/oradata1/ss02_v60/story_data01.dbf       2048                 700                  1077                 970

.
.
.

12 rows selected.


Execution Plan
----------------------------------------------------------
          0
SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=12 Bytes=816)


          1                  0
  TABLE ACCESS (BY INDEX ROWID) OF 'FILE_SIZE' (Cost=3 Card=12 Bytes=816)


          2                  1
    INDEX (RANGE SCAN) OF 'NDX_DB_NAME' (NON-UNIQUE) (Cost=1 Card=12)



Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       1397  bytes sent via SQL*Net to client
        340  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         12  rows processed

-- See, Oracle did use the index!

SQL> 
SQL> 
SQL> 
SQL>