Optimal index for SQL query. Please help!!!

[former_member396441]

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

[former_member396441]

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

[Former Member]

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

[former_member396441]

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!

[Former Member]

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"