order of columns in the primary key

[lee]

Hi,
Does it matter if the columns are changed in the Primary key?

Please help!!

Thanks.

[sreddy]

It depends. The PK create an index, if you have specific where criteria which makes use of the compsite primary key you are creating, try to create PK column order such a way it benifits your queries.

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

[jmodic]

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.