ORA-01743: only pure functions can be indexed
Cause:
While creating function based index,you should give deterministic keyword for oracle to understand the function you create
You should not give sysdate,user etc.. inside a function
01743, 00000, "only pure functions can be indexed"
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.
Solution:
The function you give to functional based index should return the same result for same parameters
eg:
Don’t
SQL> create index fn_order_date on sales(trunc(sysdate));
create index fn_order_date on sales(trunc(sysdate))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
Do
SQL> create index fn_order_date on sales(trunc(order_date));
Index created.