Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Importance of Column Order in Composite Index

Re: Importance of Column Order in Composite Index

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Thu, 01 Nov 2001 10:27:48 GMT
Message-ID: <3be12293.4269043@news>


In a valiant and sublime effort,Steven Hauser frowned, dipped a thumb in soot and doodled:

>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.
>

Ayup. Same with Peoplesoft. Absolute murder to optimize. And stupid design to start with...

In the example above, index 4 does all the work that may EVER be required from index 1, 2 and 3.

>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.

Same here, with PS.

>
>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)

Exactly. PS has the quaint habit of storing the index defs in its own dictionary. If we mess around with indexes without going and updating their own "dictionary", the darn thing will throw away any indexes it doesn't know about during one of its utility checks...

>
>And when it does finally run well SAP sends a patch or "upgrade" and
>undoes your work.

Don't you JUST love that?

:-(

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Thu Nov 01 2001 - 04:27:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US