Select on A902 table executing for longer time

[Former Member]

Hi,

I have a select query on A902 table which is executing for longer time.

   select knumh matnr
  INTO CORRESPONDING FIELDS OF TABLE lt_a902
  from a902
  for all entries in lt_cons_mtms0
  where KAPPL = 'V'
  and KSCHL = gv_kschl   " 'ZNET'
  and VKORG = p_vkorg
  and VTWEG = p_VTWEG
  and SPART = p_SPART
  and MATNR = lt_cons_mtms0-matnr
  and DATBI >= P_DATBI
  and DATAB <= p_DATAB.

When check the explain sql in ST05 transaction,the database optimiser is using the secondary index YR1.

SELECT STATEMENT ( Estimated Costs = 2 , Estimated #Rows = 1 )

       2 TABLE ACCESS BY INDEX ROWID A902
         ( Estim. Costs = 1 , Estim. #Rows = 1 )
         Estim. CPU-Costs = 25,741 Estim. IO-Costs = 1
         Filter Predicates

           1 INDEX RANGE SCAN A902~YR1
             ( Estim. Costs = 1 , Estim. #Rows = 3 )
             Search Columns: 7
             Estim. CPU-Costs = 23,857 Estim. IO-Costs = 1
             Access Predicates Filter Predicates

But this index does not have the MATNR field which is getting value from the driver internal table lt_cons_mtms0.But the primary index on A902 table has all the fields except DATAB.

So should I assume that the database optimiser is picking the wrong index and correct index would be the primary index?

This program is being used in a job chain and our basis consultants are reporting that this program is taking more than 2 hours for execution from January  this year.

What could be the reason for this?

Please give me your suggestions.

Best answers will be rewarded.

Regards,

Swapna

[adam_krawczyk1]

Hi Sawpna,

YR1 index seems to be your own customized secondary index, it would be good if you could describe it here.

In case of SELECT query only these fields from index are used, which are present in WHERE condition up to first not present field. It means that A902 has Primary Key in this order:

KAPPL, KSCHL, MATNR, OIC_MOT, ...

And your select statement uses only three fields of that:

KAPPL, KSCHL and MATNR, because there is no OIC_MOT in the WHERE condition.

You can use hints comments if you want to force particular index to be used. Also you can reorder your WHERE condition to have all fields from index at the beginning, in the same order as in index to help optimizer match the index.

Ideally index should contain all fields from query or at least most distinctive values (possible unique, no much sense to have index on fields which have only few values possible). If statement is executing still long time, try to match index exactly with more fields.

Regards

Adam

[volker_borowski2]

Hi,

in that very explain from st04/st05 doubleclick on the orange index name.

Pleas come back with

- what fields are in the index

- how many distinct valiues for each field listed

- # leaf blocks and b-level value and clustering factor

Volker

[Former Member]

HI

what are all the key fields in the a902? how many entries you have in a902

do you have company code there as a key field?

how many entries you have in this table lt_cons_mtms?

cheers

[Former Member]

Hi Swapna,

Please check that is your table A902 is a Transparent Table?

If not then do following things.

Go to technical settings > you will find there is a check box at last "Maintain as transparent table" tick on it. Then click on radio button Buffering not allowed. Save and activate technical settings.

Also if you are using secondary index then your secondary should have the same sequence as in your where condition. You can force primary/secondary index in your select query as

select knumh matnr
  INTO CORRESPONDING FIELDS OF TABLE lt_a902
  from a902
  for all entries in lt_cons_mtms0
  where KAPPL = 'V'
  and KSCHL = gv_kschl   " 'ZNET'
  and VKORG = p_vkorg
  and VTWEG = p_VTWEG
  and SPART = p_SPART
  and MATNR = lt_cons_mtms0-matnr
  and DATBI >= P_DATBI
  and DATAB <= p_DATAB

%_hints mssqlnt 'TABLE A009 abindex (primary/secondary index name)'.

Thanks

Ranjeet Singh.

   .

[arindam_m]

Hi,

A902 should be a condition Access tables. Looks like your select conditions are mostly Parameters with just the MATNR field being  common to your internal table lt_cons_mtms0.

First you can check the internal table lt_cons_mtms0 if it is IS INITIAL before executing the Query. SORT your table lt_cons_mtms0 by MATNR and DELETE the DUPLICATE entries from it before you execute this select.

Other Approach could be putting ALL your MATNR entries in a Select Option type variable and checking it with IN operator in the where clause.

Cheers,

Arindam