Function Based Indexes

[dbbyleo]

I get this error:
"ORA-01743: only pure functions can be indexed"

when I try to do this:

create index date_index
on mytable(to_date(date_column, 'MM/DD/RR'))
tablespace process_index;

See, this "date_column" is not of "date" datatype, rather of varchar, and, it's not consistent...meaning sometimes it's '8/6/02' and sometimes it's '8/06/02'...so implicit conversion doesn't work, therefore I have to use the to_date function in my queries.

I need to index this column becasue there's over 20 million rows in this table, but when I try to create a function based index with the above statement, I get the error.

Help...any sugestions?

[stmontgo]

it needs to be deterministic...that is when you execute the statement it will return the same result every time, otherwise you can't call in the fbi on this one

[dbbyleo]

Originally posted by stmontgo
it needs to be deterministic...that is when you execute the statement it will return the same result every time

Can you elaborate on the meaning? I don't understand how the function, to_date(column_name, 'MM-DD-RR') when used in a query, DOES NOT return same results every time.

[slimdave]

It's deja vu all over again!

The evaluation of the 'RR' bit depends on the current value of SYSDATE, therefore it is not "pure"

[dbbyleo]

Thank you...now that makes sense to me why one would say this function is not "pure" or not of "deterministic" manner.

So any suggestion how I can accomplish this? I needed to use 'RR' because the values on the column has '98' thru '02'.

I suppose creating a user-defined function might solve my issue, but how? I'm not the best at creating functions...

THanks