This discussion is archived
3 Replies Latest reply: Apr 20, 2013 7:58 AM by John Spencer RSS

Function Based index based on STS recommendations.

Naveed_CG Newbie
Currently Being Moderated
Hi Experts,

I ran the Oracle SQL Tuning Task on the following SQL which was taking a lot of time.


SELECT MAX(P.EFFDT) FROM
PS_PERS_DATA_EFFDT P , PS_PERS_DATA_EFFDT E
WHERE P.EMPLID = E.EMPLID
AND P.EFFDT <= :1;

After running the tuning task the finding are below

The predicate SYS_OP_UNDESCEND(SYS_OP_DESCEND("P"."EFFDT"))<=:B1 used at
line ID 8 of the execution plan contains an expression on indexed column
"SYS_NC00076$". This expression prevents the optimizer from efficiently
using indices on table "PSUSYS"."PS_PERS_DATA_EFFDT".

Recommendation
--------------
- Rewrite the predicate into an equivalent form to take advantage of
indices. Alternatively, create a function-based index on the expression.

I am not a SQL developer so please help me based on the above SQL what function-based index need to be created.

Thanks
-Naveed-

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points