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'))
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.
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
I'm stmontgo and I approve of this message
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.
Originally posted by stmontgo
it needs to be deterministic...that is when you execute the statement it will return the same result 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...
Click Here to Expand Forum to Full Width