Re: Function Based Indexes???

Paul J wrote:
>
> 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
>

You need to be running cost optimiser and have a squizz at my site

http://www.oracledba.co.uk/tips/function_indexes.htm

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"