Skip to Content
0
Former Member
Apr 05, 2013 at 12:09 PM

Select on A902 table executing for longer time

97 Views

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