I have a table cm_dt having 1,10,000 rows. Apart from other columns, the table has two date columns "start_dt" and "end_dt". I have the following indexes : IDX1 on start_dt,end_dt IDX2 on start_dt IDX3 on end_dt The following query on the table always does a full table scan The query returns only 1531 rows.

select * from cm_dt where start_dt >= to_date('01/01/2002','dd/mm/yyyy') and end_dt <=to_date('31/01/2002','dd/mm/yyyy')

Whereas an index access path on IDX1 should be most efficient. My question is what else should I do so that the index is used.

The most important point is that I cannot use and index hint because the user is free to issue any query on the table apart from the date columns. The table is perfect ly analyzed and stats are up to date.

Thanks & Regards Ray

-- Nilanjan Ray (rnilanjan@yahoo.co.in), January 16, 2004