3 Replies Latest reply: Apr 20, 2013 9:58 AM by John Spencer RSS

    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-
        • 1. Re: Function Based index based on STS recommendations.
          Karthick_Arp
          Your SQL
          SELECT MAX(P.EFFDT) 
            FROM PS_PERS_DATA_EFFDT P 
               , PS_PERS_DATA_EFFDT E
           WHERE P.EMPLID = E.EMPLID
             AND P.EFFDT <= :1;
          Why are you joing a self join on PS_PERS_DATA_EFFDT ?

          I dont see any need. Cant you just do
          select max(effdt) from ps_pers_data_effdt where effdt <= :1;
          • 2. Re: Function Based index based on STS recommendations.
            Naveed_CG
            Hi Karthick,

            This is actually a subset of a seeded SQL of People Soft One Off Cycle Program. My focus is that I wanna create the function base index based on Oracle Tunning Set Recommendatoin so If you can help on that. Appreciate for your post and reviewing.

            Thanks
            -Naveed-
            • 3. Re: Function Based index based on STS recommendations.
              John Spencer
              I suspect that the Tuning Advisor is wrong here, sys_op_undescend and sys_op_descend are internal Oracle functions that the optimizer typically applies to indexes that were created as descending:
              create index i on t (col) descending
              so those functions are applied in order for the optimizer to be able to use that index.

              You should actually look at the explain plan for that query to see what is actually doing. Richard Foote has a good explanation here http://richardfoote.wordpress.com/category/descending-indexes/

              John