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