topic Optimal index for SQL query. Please help!!! in Application Development and Automation Discussions

Optimal index for SQL query. Please help!!!

former_member396441 — Sun, 14 Oct 2012 15:58:03 GMT

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

Re: Optimal index for SQL query. Please help!!!

former_member396441 — Mon, 15 Oct 2012 15:45:19 GMT

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 sql trace

Re: Optimal index for SQL query. Please help!!!

Former Member — Mon, 15 Oct 2012 15:49:30 GMT

hello,

the field sequence matters when the Z index is being selected. You need to arrange the fields properly in the WHERE condition for a index to get picked up.

check the link

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/content.htm

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb2d446011d189700000e8322d00/content.htm

best regards,

swanand

Re: Optimal index for SQL query. Please help!!!

former_member396441 — Mon, 15 Oct 2012 15:59:43 GMT

Thanks very much Swanand! Thats really helpful.

So based on what you pointed-

Do you think this is the optimal index to modify Z02-

MANDT

WERKS

EKORG

FLIEF

LOEKZ

ZUGBA

And also get rid of Z01 and Z03 above.

Please let me know. Thanks in advance!

Re: Optimal index for SQL query. Please help!!!

Former Member — Mon, 15 Oct 2012 17:09:41 GMT

Those <> (not equal) operators are killing you. A not equal operator will kill just about any index you create. You will be better off removing those from your initial query and selecting into an internal table. Then delete the unwanted rows from the internal table as a separate step. Or you could turn those into positives by selecting the ones you do want.

Thinking about it logically, it pretty much has to search through all entries in the table to find everything that is NOT equal to your where clause.

I have a favorite saying, "NOT is not your friend"