Skip to Content

SAP hana Visualize Plan show Create Temp Index

Dear Expert,

Good morning, I would like to get some advice on Performance topic.

Our developers usually use SELECT JOIN with more then 5 tables, show some sample code : when I use ST05 to check the SQL trace, it show me :screen shot attached.

Sample code

SELECT a~matnr,         "Material Number
           a~werks,         "Plant
           e~lgort,         "Storage Location
           a~prctr,         "Profit Center
           a~lgfsb,         "Default storage location for external procurement
           b~prdha,         "Product hierarchy
           b~bismt,         "Old Material Number
           b~meins,         "Base Unit of Measure
           c~maktx,         "Material Description
           d~vprsv,         "Price Control Indicator
           d~verpr,         "Moving Average Price/Periodic Unit Price
           d~stprs,         "Standard Price
           d~peinh,         "Price Unit
           e~labst,         "Valuated Unrestricted Used Stock
           d~bklas,         "Valuation Class
           d~vmver,         "Moving Avergae Price/Periodic Unit Price in Previous Period
           d~vmstp,         "Standard Price in previous period
           f~gjahr,         "Fiscal Year
           f~gsv01,         "Total Consumption
           f~gsv02,         "Total Consumption
           f~gsv03,         "Total Consumption
           f~gsv04,         "Total Consumption
           f~gsv05,         "Total Consumption
           f~gsv06,         "Total Consumption
           f~gsv07,         "Total Consumption
           f~gsv08,         "Total Consumption
           f~gsv09,         "Total Consumption
           f~gsv10,         "Total Consumption
           f~gsv11,         "Total Consumption
           f~gsv12          "Total Consumption
      FROM marc AS a
      INNER JOIN mara AS b ON a~matnr EQ b~matnr
      INNER JOIN fins_rr_wrk_buk ON a~werks EQ fins_rr_wrk_buk~werks
      INNER JOIN makt AS c ON c~matnr EQ b~matnr
      INNER JOIN v_mbew_md AS d ON d~matnr EQ a~matnr
      INNER JOIN mard AS e ON e~matnr EQ a~matnr AND
                         e~werks EQ fins_rr_wrk_buk~werks
      INNER JOIN t001k ON d~bwkey EQ t001k~bwkey
      INNER JOIN mver AS f ON f~matnr EQ a~matnr
      INTO TABLE @lt_data
      WHERE b~mtart IN @i_mtart
      AND fins_rr_wrk_buk~bukrs EQ @i_bukrs
      AND a~matnr IN @i_matnr
      AND a~lvorm EQ ''
      AND f~gjahr IN (@l_year, @l_prev)
      AND c~spras EQ @sy-langu
      ORDER BY a~matnr, a~werks, e~lgort.

Screen shot of Execution Plan at ST05

Operation was showing JOIN (which not INDEX JOIN)

Screen shot Graphical from HAHA Studio

I would like to know all this Temporary Index, is it acceptable ?

For SELECT JOIN which more the 5 tables, is it acceptable ?

Please further assist me.

Many appreciate.

Best Regards,

Kuan

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

0 Answers

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.