RE: Function-Based Indexes and Dates

This one has been solved. It turns out there is a bug in 8.1.5 that ignores TRUNC(Date) functional indexes. It's been fixed in 8.1.6 (at least that's what Oracle says, and they're sticking to it <g>). The workaround is to create a deterministic function that simply returns TRUNC(Date) and use that in your index creation. It's not as efficient as a straight call to a built-in function, but it works under 8.1.5. The other alternative, which we're going to undertake ASAP, is to move to 8.1.6.

Regards,

Chris Gait
Oracle DBA

http://www.geocities.com/athens/3623

"Death is nature's way of saying: NEXT!"

-----Original Message-----
From: Gait, Christopher [mailto:cgait_at_condor.nrl.navy.mil] Sent: Saturday, May 20, 2000 9:54 PM
To: Multiple recipients of list ORACLE-L Subject: Function-Based Indexes and Dates

Does anyone know if there is a way (perhaps using stored functions) to get function-based indexes to work with dates instead of character comparisons? The only way I can get the optimizer to acknowledge a TRUNC(Date_Attribute) index is to 1) use a hint and 2) Add a TO_CHAR, making it to_CHAR(TRUNC(Date_Attribute)). This then messes up date comparisons from the query since what is returned is no longer a date.

-- 
Author: Gait, Christopher
  INET: cgait_at_condor.nrl.navy.mil

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).