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?

  • 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

No comments:

Post a Comment