Function Based Indexes

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?

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

Quote:

---

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.

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"

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