Question about composite index and index skip scan
849611Mar 23 2011 — edited Mar 23 2011Hi,
I have a confusion.
I have read Burleson's post on composite index column ordering (http://www.dba-oracle.com/t_composite_index_multi_column_ordering.htm) where he writes that
"......for composite indexes the most restrictive column value(the column with the highest unique values) should be put first to trim down the result set..."
But 10g performance tuning book tells this about INDEX SKIP SCAN ::
"... Index Skip scanning lets a composite index be split logically into smaller subindexes. In skip
scanning, the initial column of the composite index is not specified in the query. In other words, it is skipped.
The number of logical subindexes is determined by the number of distinct values in the initial column.
Skip scanning is advantageous if there are few distinct values in the leading column of the composite index and many distinct values in the non-leading key of the index......."
So if we design Composite indexes acc. to what Burleson said then how can we take advantage of index skip scanning. These two staements oppose each other,don't they ?
Can anybody explain this ?