Skip to Content
0
Mar 06, 2009 at 03:34 PM

Oracle insists on using particular index in SELECT -- HELP!!!

65 Views

Here's the situation: Back in November, I wrote a report program that contained the following SELECT statement on SAP table FMIFIIT:

* Get data from the FMIFIIT table.
  SELECT trbtr
         fmbelnr
         fmbuzei
         fonds
         fistl
         farea
         measure
         fipex
         zhldt
         sgtxt
         grant_nbr
    FROM fmifiit
    INTO TABLE it_fmifiit_data
   WHERE zhldt >= p_bpdte                            AND
         zhldt <= p_epdte                            AND
         fistl IN r_fndctr                           AND
         fistl BETWEEN '4011000000' AND '4013999999' AND
         fonds IN r_fund                             AND
         fipex BETWEEN '500000' AND '599999'         AND
         measure <> 'GRANT RELEVANT'                 AND
         measure IN s_rio                            AND
         wrttp NOT IN ('51','60','81')               AND
         stats = ' '.

I ran into time-out issues with this statement, so a new index ("Z5") was created on the following fields: ZHLDT, FISTL, FONDS, and FIPEX. This was the 11th custom index added to the table. The program was re-run, the statement picked up the new index, and it ran much faster. Problem solved.

Fast-forward to this week. I wrote a new report program that used a SELECT statement virtually identical to the one above. It looks like this:

* Get FMIFIIT data.
  SELECT gjahr
         knbelnr
         knbuzei
         measure
         trbtr
         fmbelnr
         fmbuzei
         fonds
         fistl
         farea
         fipex
         zhldt
         sgtxt
      FROM fmifiit
      INTO TABLE it_fmifiit
   WHERE zhldt >= p_bpdte                    AND
         zhldt <= p_epdte                    AND
         fistl IN r_fndctr                   AND
         fonds IN r_fund                     AND
         fipex BETWEEN '500000' AND '599999' AND
         measure <> 'GRANT RELEVANT'         AND
         measure IN s_rio                    AND
         wrttp NOT IN ('51','60','81')       AND
         stats = ' '.

Again, the program began running into time-out issues on this statement. I did a trace using ST05, and I found that the system was choose index "3" (FONDS, FIPEX, FIPOS) instead of the obviously-better "Z5" index created a few months ago.

I tried all kinds of things to figure out why it was choosing "3" to no avail. Basis even re-ran statistics on the FMIFIIT table, but that didn't work. Right now, my only option is to add an Oracle hint to the SELECT statement to force it to use index "Z5". In our DEV system, I've used SE30 to verify that this definitely improves the timing bottleneck that occurs when "3" is used.

I've only been an ABAP programmer for a few years now, and this one is technically beyond my knowledge. I don't know what else to try, and I don't know how to determine why the system keeps choosing the index it does. It's especially frustrating when the SELECT statement is so similar to the one from above, and in that case, it DOES pick up the "Z5" index.

Does anyone have any information/strategies/suggestions? At this point, I'm all ears.

Thanks so much!

Dave