Function based indexing for trunc

scharan07 · #1 (**permalink**) 08-30-10, 05:00

Hi,

i have calculated columns which is done on single column against current date trunc(currtent_date-due_date).

because of performance issue i am trying to create the function based indexing,
becuase i have used trunc oracle is not allowing indexing on this columns.

Quote:

SQL Error: ORA-01743: only pure functions can be indexed.

i tried to create index (current_date-due_date) same error is occurred.

is there any alternative way to achieve this..?

shammat · #2 (**permalink**) 08-30-10, 05:50

Please show us the full CREATE INDEX statement, as we cannot see your screen from here.
(Ideally show us the full definition of the table as a CREATE TABLE as well)

Quote:

i tried to create index (current_date-due_date) same error is occurred.

You can't index an expression that changes every day.

scharan07 · #3 (**permalink**) 08-30-10, 06:24

This is my index creation code.

Code:

CREATE INDEX NOOFDAYS_INDEX ON MY_TABLE((CURRENT_DATE-DUE_DATE));

SO YOUR SAYING THAT IS NOT POSSIBLE TO CREATE AN INDEX WHICH VALUE IS CHANGE DAILY.

shammat · #4 (**permalink**) 08-30-10, 06:46

Quote:

Originally Posted by scharan07

TSO YOUR SAYING THAT IS NOT POSSIBLE TO CREATE AN INDEX WHICH VALUE IS CHANGE DAILY.

Correct.

It would actually change every second as CURRENT_DATE includes the time as well.

How do you expect that to work? That Oracle automagically re-creates the index each time the expression changes? With your example, Oracle would need to re-create the entire index every second to recalculate the expression....

And please do not write in all upper-case. That is consider rude (shouting).