In a concatenated Index, the order of columns are important. The leading (first) column should be of a high cardinality value, followed by other columns in that order.
Let us consider an example:
Table T has 5 columns, viz colA, colB, colC , colD and colE. Now you want a concatenated index on columns colC + colB+ColE.
Before creating the index, check the distinct values of columns C, B and E.
C has 200 distinct values, B has 400 and E has 1000 values.
In that case, I would prefer to create the index with ColE+ColB+ColC.
Because of the high cardinality in the first columns, your index will be evenly balanced.
Originally posted by tamilselvan In a concatenated Index, the order of columns are important. The leading (first) column should be of a high cardinality value, followed by other columns in that order.
With the appearance of index compression in Oracle8i, the order of columns should be just the opposite if the cardinality is the only deciding factor. Columns with least cardinality should be the leading columns in the composite index and not the other way around.
So in tamiselvan example the order of columns in index should be C-B-E and not E-B-C. And of course index should be compressed.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?