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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s