Ajit Aranha wrote:
> 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?
Within Version 7.3 SAP DB only uses indexes if the amount of searched pages
is smaller then a certain threshold.
This threshold is defined via the db parameters OPTIM_FETCH_RESLT and
OPTIM_BUILD_RESLT.
Both parameters have a default of 15 which means that the index is only used if the
rate of
searched pages to total index pages is smaller or equal 15%.
If it is greater we assume that the overhead of the index access is to big
and therefor read the data via key access or table scan.
You could try to increase both parameters to get an index access.
BTW with version >= 7.4 we removed those parameters and compare all possible access
strategies
(index, primary key and table scan) with each other and chose the best one.
So perhaps you should upgrade to 7.4.
Kind regards,
Holger
SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
