Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Question about composite index and index skip scan

849611Mar 23 2011 — edited Mar 23 2011
Hi,
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 ?
This post has been answered by Hemant K Chitale on Mar 23 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 20 2011
Added on Mar 23 2011
9 comments
1,135 views