Re: A query: anyone know what's up with indexes on date fields?

In article <1993Feb12.180554.3445_at_cs.odu.edu> aiko_at_opium.cs.odu.edu (John K Hayes) writes:

>Hi. I'm trying to speed up performance of a forms query where
>substr(database-date-field,1,9) = <value-of-forms-date-field>
 

>So I created an index on the database-date-field. Trouble is it doesn't
>seem to help any. I think the problem *may* be that I'm using the substr
>in the where clause - it doesn't seem to use the index when you use substr
>(??? not sure ???).

Correct. It doesn't use an index if you put the database field in a function.

There are a few reasonably simple ways to solve your problem.

1. Store date and time in separate columns. This is probably a good idea especially if you are going to modify the records with the form because SQL*Forms dates are all rounded to the nearest day.
2. Depending on the exact context, you may be able to change your condition to be

database-date-field BETWEEN <value-of-forms-date-field> AND

                                <value-of-forms-date-field> + 1

Good luck.
Mike Received on Sat Feb 13 1993 - 01:13:55 CET