I have a select query on A902 table which is executing for longer time.
select knumh matnr
INTO CORRESPONDING FIELDS OF TABLE lt_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
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.