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

[former_member210148]

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

[former_member204746 · Accepted solution]

I am having the exact same problem, but obviously on another table/index.

a temporary workaround:

rebuild index online and then recalc DB stats on that table. I need to do this about every 3 months...

I have not found a permanent solution as of today.

[lbreddemann · Accepted solution]

> I am having the exact same problem, but obviously on another table/index.

Than it's quite likely that it is not the same issue at all.

Describing the problem just with "wrong index is taken" is too general here.

> rebuild index online and then recalc DB stats on that table. I need to do this about every 3 months...

Ok, first thing: when you rebuild an index with Oracle 10g you don't need to gather stats afterwards, as they are automatically gathered during the index rebuild.

Question is: is it the index rebuild that yields the advantage or simply the new statistics with lower leaf blocks?

In that case you may check on the data loading/unloadin characteristics of the table. Perhaps you can simply lock the good statistics and spare the rebuild further on.

regards,

Lars

[lbreddemann]

Hi Dave,

based on the query I cannot share your view that there is an obvious choice.

In fact the best index choice depends on what your data looks like and how are the In-lists filled.

Maybe the range conditions are very useful, maybe the in-lists are the most selective criteria.

As a detailed analysis of this is a bit heavy to be done via this forum, why don't you just implement the hint and live happily with it?

Since you find your index sensible and have proven that it leads to better performance, you obviously know more about your data than Oracle does by the statistics.

Now you can either try and learn how the Oracle CBO works (there are notes and books available on this topic) or you just use your workaround although you may not exactly understand the reasons for the problem.

In general - if this would be a support message - I would ask you to ensure that all parameter recommendations are implemented and all CBO patches are installed.

Then I'd use the sql-statement data collection script (there's a sap note containing this script - just search for 'sql_id') to gather much of the relevant data.

And then I'd have to look into the data and figure out what's behind the CBOs decision.

As you wrote that you're rather on the ABAP side of live this effort may not necessarily be worthwhile when you have a proven workaround at hand.

regards,

Lars