Re: A query: anyone know what's up with indexes on date fields?
Date: Fri, 12 Feb 1993 21:51:52 GMT
Message-ID: <ALAW.93Feb12135152_at_ap221sun.oracle.com>
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.7299Received on Fri Feb 12 1993 - 22:51:52 CET