topic Re: Wrong index picked in Application Development and Automation Discussions

Wrong index picked

adhimassetianeg — Tue, 12 Feb 2008 19:37:38 GMT

Why did sometimes SAP choose a wrong index for a table?

We were creating reports that takes data from DFKKOP and IST_TDATA tables. These table contains millions of data. E.g. in one month, the table DFKKOP grows from 0 records to 12 millions of records. So index takes very important part here.

We had created additional indexes to improve query performance, but somehow SAP ignores these indexes and use full table scan. We had tried several methods, i.e:

- Fields order in WHERE clause is the same as in the index.

- Using Oracle HINTS INDEX .

- Using Oracle HINTS FIRST_ROWS.

- Update table statistic

- Adjusting the index (SE14)

Sometimes the right index can be chosen by using HINTS INDEX, but sometimes we have to use the FIRST_ROWS hints, and sometimes none can give the right index. In a unique case I have a query with 1-2 records in FOR ALL ENTRIES internal table that SAP can use the right index. But when the internal table in FOR ALL ENTRIES contains 3 or more records, the same query use full table scan which is extremely slow.

We are using ECC 6 and Oracle 10g.

Please help... Is there notes or fix that I should implement?

Re: Wrong index picked

Former Member — Tue, 12 Feb 2008 19:46:29 GMT

Sounds odd for sure - please provide the Open SQL that you are running... and the code that populates the fields used in the WHERE clause of that SQL.

Re: Wrong index picked

Former Member — Tue, 12 Feb 2008 19:49:11 GMT

Well, SAP already has created six secondary indexes for this table. Sometimes adding more indexes, particularly when they are similar to existing ones just confuses the database optimizer into picking the wrong one.

Also, if you are selecting large amounts of data, the database may decide to do a full table scan anyway.

Can we see your SELECT?

Rob

Re: Wrong index picked

Former Member — Wed, 13 Feb 2008 22:08:13 GMT

Adhimas,

Do you have some SQL that we can review?

If you have solved your problem on your own, please close this thread.

Re: Wrong index picked

Former Member — Thu, 14 Feb 2008 11:21:50 GMT

About the last unique problem, here is the code:


  data:
    begin of it_vkont occurs 100,
      gpart like fkkvkp-gpart,
      vkont like fkkvkp-vkont,
    end of it_vkont,

    begin of it_object_id occurs 0,
      vkont like ist_tdata-vkont,
      object_id like ist_tdata-object_id,
    end of it_object_id.

* -- for snote contains 3 records, this query will return 3 records  
  select distinct vkont object_id into table it_object_id from ist_tdata
    where object_id in snotel
    %_hints oracle 'FIRST_ROWS'.

  if it_object_id[] is initial.
    message e000(0k) with 'Phone number does not exist'.
    return.
  endif.

* -- for snote contains 3 records, this query will also return 3 records  
  select gpart vkont appending table it_vkont from fkkvkp
    for all entries in it_object_id
    where vkont = it_object_id-vkont.

  if it_vkont[] is initial.
    message e000(0k) with 'Contract Account does not exist'.
    return.
  endif.

* -- for snote contains 3 (or more) records, SAP will choose a wrong index 
  select distinct gpart vkont opbel opupk
         bukrs blart waers budat
         bldat betrw hvorg tvorg
  into table t_data
  from dfkkop
  for all entries in it_vkont
  where gpart = it_vkont-gpart
    and faedn in sfaedn
    and waers in swaers
    and applk eq 'T'
    and bukrs eq '1000'
    and augst eq space
    and augbl eq space
    and ( tvorg ne '0500' or tvorg ne '0420' )
    and blart in ('IP', 'IF', 'IN', 'YY')
    %_hints oracle 'INDEX(DFKKOP"Z04")'.
    
  sort t_data by gpart vkont opbel opupk.

For workaround I have replaced the last query to a bad query performance practice but works pretty well in our case.


  refresh t_data.
  loop at it_vkont.
    select distinct gpart vkont opbel opupk
           bukrs blart waers budat
           bldat betrw hvorg tvorg
    appending table t_data
    from dfkkop
    where gpart = it_vkont-gpart
      and faedn in sfaedn
      and waers in swaers
      and applk eq 'T'
      and bukrs eq '1000'
      and augst eq space
      and augbl eq space
      and ( tvorg ne '0500' or tvorg ne '0420' )
      and blart in ('IP', 'IF', 'IN', 'YY')
      %_hints oracle 'INDEX(DFKKOP"Z04")'.
  endloop.
  
  sort t_data by gpart vkont opbel opupk.
  delete adjacent duplicates from t_data.

The index Z04 in DFKKOP contains these ordered fields :

GPART Business Partner Number
FAEDN Due date for net payment
WAERS Transaction Currency
APPLK Application area
BUKRS Company Code
AUGST Clearing status
AUGBL Clearing Document or Printed Document
TVORG Subtransaction for Document Item
BLART Document Type

Re: Wrong index picked

Former Member — Thu, 14 Feb 2008 15:14:04 GMT

Well there a couple of errors in the WHERE, one obvious and the other not so obvious:

      and ( tvorg ne '0500' or tvorg ne '0420' )

You probably mean:

      and ( tvorg ne '0500' and tvorg ne '0420' )

And using "NE" for that condition will prevent the SELECT from using that field or any field that follows it in the index.

Maybe this will be better:

TABLES: dfkkop, tfktvo.

RANGES: r_tvorg FOR dfkkop-tvorg.

* Get all values of tvorg into a range table.

MOVE 'EQ' TO r_tvorg-option.
MOVE 'I'  TO r_tvorg-sign.
SELECT tvorg FROM  tfktvo
  INTO r_tvorg-low.
  APPEND r_tvorg.
ENDSELECT.

REFRESH t_data.
LOOP AT it_vkont.
  SELECT DISTINCT gpart vkont opbel opupk
         bukrs blart waers budat
         bldat betrw hvorg tvorg
  APPENDING TABLE t_data
  FROM dfkkop
  WHERE gpart = it_vkont-gpart
    AND faedn IN sfaedn
    AND waers IN swaers
    AND applk EQ 'T'
    AND bukrs EQ '1000'
    AND augst EQ space
    AND augbl EQ space
    AND tvorg IN r_tvorg                    "<====
    AND blart IN ('IP', 'IF', 'IN', 'YY').
ENDLOOP.

SORT t_data BY gpart vkont opbel opupk.
DELETE ADJACENT DUPLICATES FROM t_data.

Rob

Re: Wrong index picked

ThomasZloch — Thu, 14 Feb 2008 15:57:04 GMT

Hello,

your syntax for the Oracle hints looks odd, I have been using this syntax successfully:

%_HINTS ORACLE 'INDEX("BKPF" "BKPF~5")'

that would translate into

%_HINTS ORACLE 'INDEX("DFKKOP" "DFKKOP~Z04")'

for your particular case. Maybe worth a try.

Cheers

Thomas

Re: Wrong index picked

Former Member — Thu, 14 Feb 2008 16:17:03 GMT

Rather than using HINTS, I think he should see if he can get it to use the proper index without it. That way, if the database statistics change dramatically, it won't be forced into picking the wrong index.

Rob

Re: Wrong index picked

ThomasZloch — Thu, 14 Feb 2008 16:24:07 GMT

Agreed. Hints should only be used when all other options to construct a proper select statement were unsuccessful. Since these guys have created their own Z04 index, this might be a case for forcing that index to be used. In my example I have quoted, I had to force the CBO to use BKPF~5 index (CPUDT), because due to an Oracle ~~bug~~ feature it would not use this best access path for a particular query. And we have about 100 million records in BKPF.

Cheers

Thomas

Re: Wrong index picked

Former Member — Fri, 15 Feb 2008 08:17:30 GMT

Thank you for all your suggestions. I have tried changing the NE to using a IN just as your example in another case with no success in finding the right INDEX. But I'll try again for this case and I will also try changing the syntax for HINTS.

I will let you know the results by monday because today I'm not in the office.

Re: Wrong index picked

adhimassetianeg — Sun, 17 Feb 2008 10:33:55 GMT

I haven't tried the suggestions but during sap notes search I found this interesting article that I think I should share with you all. The note number is 176754.

-adhimas-