09-04-2013 8:00 AM
Hi folks,
here i am fetching the data by using the view WB2_V_MKPF_MSEG2. But am not using the posting date (BUDAT) at where condition in the select stetement as per my requirement. So it needs to fetch the entire records available. For this execution it is almost taking 3 - 4 hours time. Please suggest me how to tune this up based on irrespective posting date at where condition in the select statement. below is the statement where getting struck for long time. below is the code.
SELECT xblnr werks_i matnr_i bwart_i menge_i dmbtr_i xauto_i budat umwrk_i mblnr mjahr
FROM wb2_v_mkpf_mseg2
INTO TABLE it_view1
WHERE MBLNR BETWEEN 4900000000 AND 4999999999
AND MBLNR_I BETWEEN 4900000000 AND 4999999999
AND ZEILE_I BETWEEN 1 AND 9999
AND MJAHR BETWEEN 2010 and 9999
AND MJAHR_I BETWEEN 2010 and 9999
* AND budat IN s_date
AND matnr_i IN s_matnr
AND werks_i IN s_werks
AND bwart_i IN ('ZE5','ZX5','ZD5','ZS5')
%_HINTS ORACLE '&max_in_blocking_factor 100&'.
09-04-2013 9:14 AM
Hello Naresh.
1) If primary key(s) is available, use that to fetch the data into internal table.
Perform your filtering,deleting and other operations in the internal table.
2) Else if secondary index(s) is available, use that to fetch the data into internal table.
Perform your filtering,deleting and other operations in the internal table.
Regards.
09-04-2013 10:17 AM
Hi Naineni,
There are some redundant where conditions in the query. For example, the definition of the view says "MKPF~MBLNR = MSEG~MBLNR"; hence, the condition "MBLNR_I BETWEEN 4900000000 AND 4999999999" is redundant.
The conditions of "ZEILE_I" and "MJAHR_I" are also redundant.
Maybe the query optimizer gets confused about those where clauses and chooses a bad execution plan.
09-04-2013 10:28 AM
I don't think the optimizer will be able to do many optimization for a request on such a view.
Could you use a JOIN between MEG and MKPF and remove redundant and trivial criteria. Then as it will find many indexes on MSEG, optimizer may get better result than reading the whole view sequentially.
Regards,
Raymond
09-05-2013 11:40 AM
Hi friends,
Yes I commented those redundant fields and even i tried with joining the MSEG and MKPF instead of this VIEW but still no use. it is taking some hours to get executed the query unless we give the BUDAT at where condition. There are around 90 lakhs of records available in the view. So Obviously it is taking time to fetch all of them. Can we optimize this, Please help me.
Regards
Naresh
09-05-2013 1:51 PM
Hi naresh ,
Writing select statement for database view and using join statement are the same . so you will not find any difference .
try to select MKPF and MSEG separately using for all entries and then you can join that using read statement in new internal table .
try out :this seems to be like between
RANGES : S_MBLNR FOR MKPF-MBLNR.
S_MBLNR-sign = 'I'.
S_MBLNR-option = 'EQ'.
S_MBLNR-low = '4900000000'.
APPEND S_MBLNR.
S_ MBLNR-sign = 'I'.
S_MBLNR-option = 'EQ'.
S_MBLNR-high = '4999999999'.
APPEND S_MBLNR.
like this try for ZEILE_I, MJAHR . Mostly we try not to use join or query for database view .
feel free to convey your view on this .
Regards,
Sanjith N