Index on date columns and query execution stratergy

"Ajit Aranha" <[email protected]> Thu, 11 Sep 2003 12:11:46 +0530
Newsgroups gmane.comp.db.sapdb.general
Message-ID <[email protected]>
Hi,

I'm using ORACLE mode and sapdb 7.3.30

I have a composite index on the table rt_delivery (to_be_delivered,
delivered_timestamp)

to_be_delivered is number(1)
delivered_timestamp is date (i.e. timestamp in internal mode)

when explaining my query

explain select * from rt_delivery where to_be_delivered=0 and
delivered_timestamp > to_date('20030428','YYYYMMDD') and
delivered_timestamp < to_date('20030430','YYYYMMDD')

uses the above mentioned index

However 
	

explain select * from rt_delivery where to_be_delivered=0 and
delivered_timestamp > to_date('20030328','YYYYMMDD') and
delivered_timestamp < to_date('20030430','YYYYMMDD')

uses a table scan
(note the to_date('20030328'.. above. The interval being more than a
month causes the problem)

Why is this and how do I optimize if I'm searching an interval of more
than a month?
Regards,
Ajit