on 10-14-2012 6:04 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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!
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.