ORA-01743: only pure functions can be indexed

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.

Leave a Reply

%d bloggers like this: