I have a Composite Primary key which is combination of Column1, and Column2. What would be the difference if I have Composite key with Column2, and Column1. Does it even really matter which order you set columns?
2 Answers
It does, but perhaps not in the way you might expect.
Given col1 and col2, people's initial instinct is to put the more selective column first.
But, that's almost always irrelevant.
First, you need to consider how the index will be used in your SQL statements. If some SQLs only specify col1, then col1 should probably be first. If all SQLs will always specify both columns in the where predicate, then it doesn't matter which comes first.
But, what if most of the SQLs specify both col1 and col2, and a few provide only col1 or col2?
Well, in that case, the least selective column should (probably) go first. There are two reasons for this. First, if the least selective goes first, then, for those queries where only the second column is specified in the predicate, Oracle will be more likely to do an INDEX SKIP SCAN, if the leading column is not very selective. Second, by putting the less selective column first, you'll be more likely to be able to take advantage of index compression.
Hope that helps.
This is really a question of how your users will be accessing the data. By composite key - I am assuming these columns to be the unique identifier for accessing rows in this table. If so this creates a unique index over these columns.
Traditionally, the most selective columns are used first in an index. And it would make sense for this approach to be taken to offer the best in performance.
However, where index hotspots have been detected - the index can be reversed.
The index may also be affected if either of the columns are missing in the WHERE clause when accessing data.
