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

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 ???). And of course, it won't let you create an index on
>a substr'd field (dammit).
 

>*And* of course, the only way to query by date and get any results is to
>use the substr. Since date/time values are intrinsically unique, how
>the hell am I supposed to pull records with common dates (ignoring the
>time portion) utilizing an index so I don't fall asleep in the process????
>(It's a huge file, by the way....).

Using substr() for date is inherently dangerous. If your DBA decides to change to date format from 'DD-MON-YY' to 'DD-MON-YYYY', your join will fail.

Instead you should use trunc(database-date-field). This will truncates the time stamp from the date field.

Now, to utilize the index, maybe you can try this

	database-date-field between trunc(value-of-forms-date-field) and
		trunc(value-of-forms-date-field+1)

--
 Alvin W. Law ........................................... Oracle Corporation
 Senior Applications Engineer ............... 300 Oracle Parkway, Box 659306
 Oracle Manufacturing ............................. Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ....... Voice: 415.506.3390 ...... Fax: 415.506.7299