- In general, when using a multi-column index, you want to put the most restrictive column value first (the column with the highest unique values) because this will trim-down the result set.
- Because Oracle can only access one index, your job is to
examine your historical SQL workload and build a single
composite index that satisfies the majority of the SQL queries.
- The Oracle optimizer may try to make single column
indexes behave as-if they were a single composite index.
Prior to 10g, this could be done with the "and_equal" hint.
- Beware that indexes have overhead
- You can run scripts to monitor the invocation count for each column in a multiple column composite index
Friday, July 13, 2012
how to determine the optimal column order for a composite index
What is the secret for creating a composite index with the columns in the proper sequence?