Re: Importance of Column Order in Composite Index

From: Steven Hauser <hause011_at_garnet.tc.umn.edu>
Date: 31 Oct 2001 09:24:55 -0600
Message-ID: <9rp547$k34$1@garnet.tc.umn.edu>

SAP like most ERP software has way too many indexes on tables of the form:

index1: field1, field2, field3, field4
index2: field1, field2, field3, field4,field5
index3: field1, field2, field3, field4,field5,field6
index4: field1, field2, field3, field4,field5,field6,field7
index5: field1, field2, field3, field8,field5,field6,field7
index6: field1, field2, field3, field8,field5,field9,field7

...
...

index12: ...

Fields 1 through x will typically have low cardinality.

Usually for commonly run batch programs and queries the SQL has been beaten with hints and twisted to get some performance by repeated SAP patches. But I have occasionally put an index in with high cardinality in the first field and gotten factor of 10 performance gains.

But the results must be tested system wide, as messing with indexes may affect other parts of the house of cards that is SAP.(or any other ERP)

And when it does finally run well SAP sends a patch or "upgrade" and undoes your work. Received on Wed Oct 31 2001 - 09:24:55 CST