3 Replies Latest reply on Apr 20, 2013 2:58 PM by John Spencer

    Function Based index based on STS recommendations.

    Naveed_CG
      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-