cancel
Showing results for 
Search instead for 
Did you mean: 

EBAN table z optimal index for SQL! Please help!

0 Kudos

Hi gurus-

Can you please guide me what is the optimal z index to create for the following sql query (ME59N) transaction--

EBAN table.

Running Oracle 11.2g

SAP ECC 6

IS Retail

Also, does the field order matter when creating z index.

Thanks in advance!!!!

SQL Statement

SELECT
  "MANDT" , "BANFN" , "BNFPO" , "BSART" , "BSTYP" , "BSAKZ" , "LOEKZ" , "STATU" , "ESTKZ" ,
  "FRGKZ" , "FRGZU" , "FRGST" , "EKGRP" , "ERNAM" , "ERDAT" , "AFNAM" , "TXZ01" , "MATNR" ,
  "EMATN" , "WERKS" , "LGORT" , "BEDNR" , "MATKL" , "RESWK" , "MENGE" , "MEINS" , "BUMNG" ,
  "BADAT" , "LPEIN" , "LFDAT" , "FRGDT" , "WEBAZ" , "PREIS" , "PEINH" , "PSTYP" , "KNTTP" ,
  "KZVBR" , "KFLAG" , "VRTKZ" , "TWRKZ" , "WEPOS" , "WEUNB" , "REPOS" , "LIFNR" , "FLIEF" ,
  "EKORG" , "VRTYP" , "KONNR" , "KTPNR" , "INFNR" , "ZUGBA" , "QUNUM" , "QUPOS" , "DISPO" ,
  "SERNR" , "BVDAT" , "BATOL" , "BVDRK" , "EBELN" , "EBELP" , "BEDAT" , "BSMNG" , "LBLNI" ,
  "BWTAR" , "XOBLR" , "EBAKZ" , "RSNUM" , "SOBKZ" , "ARSNR" , "ARSPS" , "FIXKZ" , "BMEIN" ,
  "REVLV" , "VORAB" , "PACKNO" , "KANBA" , "BPUEB" , "CUOBJ" , "FRGGR" , "FRGRL" , "AKTNR" ,
  "CHARG" , "UMSOK" , "VERID" , "FIPOS" , "FISTL" , "GEBER" , "KZKFG" , "SATNR" , "MNG02" ,
  "DAT01" , "ATTYP" , "ADRNR" , "ADRN2" , "KUNNR" , "EMLIF" , "LBLKZ" , "KZBWS" , "WAERS" ,
  "IDNLF" , "GSFRG" , "MPROF" , "KZFME" , "SPRAS" , "TECHS" , "MFRPN" , "MFRNR" , "EMNFR" ,
  "FORDN" , "FORDP" , "PLIFZ" , "BERID" , "UZEIT" , "FKBER" , "GRANT_NBR" , "MEMORY" , "BANPR" ,
  "RLWRT" , "BLCKD" , "REVNO" , "BLCKT" , "BESWK" , "EPROFILE" , "EPREFDOC" , "EPREFITM" ,
  "GMMNG" , "WRTKZ" , "RESLO" , "KBLNR" , "KBLPOS" , "PRIO_URG" , "PRIO_REQ" , "MEMORYTYPE" ,
  "ANZSN" , "MHDRZ" , "IPRKZ" , "NODISP" , "SRM_CONTRACT_ID" , "SRM_CONTRACT_ITM" , "BUDGET_PD" ,
  "ADVCODE" , "STACODE" , "BANFN_CS" , "BNFPO_CS" , "ITEM_CS" , "BSMNG_SND" , "NO_MARD_DATA" ,
  "SERRU" , "DISUB_SOBKZ" , "DISUB_PSPNR" , "DISUB_KUNNR" , "DISUB_VBELN" , "DISUB_POSNR" ,
  "DISUB_OWNER" , "IUID_RELEVANT"
FROM
  "EBAN"
WHERE
  "MANDT" = :A0 AND "WERKS" IN ( :A1 , :A2 , :A3 , :A4 , :A5 ) AND "BSAKZ" <> :A6 AND "FLIEF" = :A7
  AND "EKORG" = :A8 AND "VRTYP" <> :A9 AND "LOEKZ" = :A10 AND "ZUGBA" = :A11

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Masood,

The optimal index should be matched all the fields in the sql statement where condition. But, if you are talking about ME59N transaction, standard indexes will be enough on the EBAN. Please note that the more index causes the more long runtime for the DML query. Because of this, please check the OSS performance notes about EBAN table and ME59N and consult to the SAP, before you create the index on the standard table.

    

>> Also, does the field order matter when creating z index.

In general, yes the order is important. You can check the "index column order" on the internet to find further information.

Best regards,

Orkun Gedik

Answers (2)

Answers (2)

0 Kudos

Thanks Stefan and Orkun! Appreciate the repsonse very much.

We have the following scenario.

Oracle Database was upgraded to 11.2g

11.0.2.0.2.0

Applied the patch set 11.0.2.0.2.7 that included CBO Merge note. Still the optimizer is choosing not the most effieiecient index-

Z index for EBAN table currently-

Z01

MANDT Client

WERKS Site

EKGRP Purchasing Group

BSAKZ Control indicator for purchasing document type

FLIEF Fixed Vendor

Z02

MANDT Client

ZUGBA Assigned Source of Supply

LOEKZ Deletion Indicator in Purchasing Document

WERKS Site

FLIEF Fixed Vendor

Z03

MANDT Client

MATNR Article Number

WERKS Site

LOEKZ Deletion Indicator in Purchasing Document

EBAKZ Purchase Requisition Closed

BSAKZ Control indicator for purchasing document type

NODISP Ind: Reserv. not applicable to RP;Purc. req. not created

SOBKZ Special Stock Indicator

PSTYP Item Category in Purchasing Document

We thought, Z02 is the most optimized index the SQL will choose that but the trace Explain plan chooses Z01 instead and caused performance problem.

I'm also attaching the

 

Former Member
0 Kudos

Oracle builds the proper path to access to the requested data set, by using database statistics. So, the execution plan has been created according to the database statistics. To compare the results, you can give a hint to the CBO on ST05 and see the result. Please note that the database may not be select z02 index even you give the hint. But this the way to compare both results.

On the other hand be sure that the update stats job executed successfully on DB13 transaction.

Best regards,

Orkun Gedik

0 Kudos

Thanks Orkun!

We tried with the Rule Based Optmizer by alter session to force it to choose Z02 but in the explain plan, it still shows Z01 and in oracle the sql can not be traced.

Do you know a way to trace the sql in oracle with alter session command with a index hint?

Also, we updated the statistics and its current as of yesterday.

The question is if Z01 is selected becasue WERKS, EKGRP are specified in Input variant but in case of Z02, the input variant does not contain ZUGBA, LOEKZ, WERKS in the same order?

Please let me know.

Thanks again!

stefan_koehler
Active Contributor
0 Kudos

Hi Masood,

once again .. not enough information. You are just looking at the predicates, but not at the work that has to be done (and some optimized access path like access or filter predicates) and possible query transformations.

The columns ZUGBA and LOEKZ has only two distinct values each - which means that each density is only 0.5 (disregarding histograms and disabled bind variable peeking in that environment). If the index Z02 has much more leaf blocks it could be a valid reason for not using it.

The index range scan can be much less work in the B*Tree structure on index Z01 (if we have much less leaf blocks), because of the "highly" selective column WERKS (as second column). I guess that the ACCESS predicate stops at this column and applies the other columns as FILTER.

The column order (of the AND predicates) does not matter in your case. The IN LIST itself is transferred into OR concatenations.

However all of these stuff is only "theoretical", because of the lack of information.

Regards

Stefan

P.S.: Soooo tired of reading suggestions about updating the statistics ... fighting the issue without knowing the root cause ... shooting silver bullets ... or lack of know-how

0 Kudos

Thanks Stefan! I sent you an email on your soocs.de account. Please check and let me know what you think.

Thanks again!

stefan_koehler
Active Contributor
0 Kudos

Hello Masood,

> Does the field order matter when creating z index.

Sometimes. Details can be found in Sapnote #825653 / Subpoint 10. However in your case it matters, but the provided information is too less to make some suggestions.

> Can you please guide me what is the optimal z index to create for the following sql query?

Suggestions can only be done, if we know the issue and have enough information (like execution plan, filter / access predicates, statistics, used leaf blocks and so on).

Regards

Stefan