Skip to Content
author's profile photo Former Member
Former Member

Performance issue with the view WB2_V_MKPF_MSEG2

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&'.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on Sep 04, 2013 at 08: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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 04, 2013 at 09: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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 04, 2013 at 09: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 05, 2013 at 10: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 05, 2013 at 12: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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.