Function Based Indexes???

Hi All,

I've created a function based index on a table, changed the compatibility flag in the initialisation file, and created my function (used in the index) as deterministic.

But, I still can't get a simple query to use the created index to help speed up retrieval time...

Want am I doing wrong???? Anyone?

Function is:

CREATE FUNCTION MONTH_TRUNC(p_DATE IN DATE) RETURN DATE DETERMINISTIC AS

BEGIN
  RETURN (TRUNC(p_DATE-0.25-(1/1440),'MONTH')+0.25+(1/1440)); -- Offset to 6:01am
END MONTH_TRUNC; Table is (for example):

PROMPT Creating Table 'DATA_VALUES'
CREATE TABLE DATA_VALUES
 (DVA_ID NUMBER(10) NOT NULL
 ,READING_DATE DATE NOT NULL
 )
 TABLESPACE USER_DATA
/

PROMPT Creating Primary Key on 'DATA_VALUES' ALTER TABLE DATA_VALUES
 ADD CONSTRAINT DVA_PK PRIMARY KEY
  (DVA_ID)
 USING INDEX
 TABLESPACE USER_INDEX
/

Index is:

CREATE INDEX DATA_VALUES_MONTH_I ON DATA_VALUES (MONTH_TRUNC(READING_DATE)) TABLESPACE USER_INDEX; Simple Query is:

SELECT MONTH_TRUNC(READING_DATE) DATE_VALUE, FROM DATA_VALUES
WHERE MONTH_TRUNC(READING_DATE) = &v_DATE
/

Can anyone help, or suggest anything....

Thanks.
Paul

--