Specialized Indexes - Expert Indexing in Oracle Database 11g - page 101

If the leading portion of a composite index has a small number of distinct values and the non-
leading portion of the index contains a large number of distinct values, skip scanning proves useful.
Let's use a simple example to demonstrate how index skip scanning works. The test query is as follows:
select * from customers where cust_email=' [e-mail]';
The customers table also has a column named GENDER , which can, of course, take just two values: M
and F. Here's a sample of the composite index's entries from an index block:
F, [e-mail],r owid
F, [e-mail],r owid
F, [e-mail],r owid
F, [e-mail],r owid
F, [e-mail],r owid
F, [e-mail],r owid
M, [e-mail],r owid
M, [e-mail],r owid
Say you issue a query that only specifies the CUST_MAIL column, and not the leading column GENDER ,
in its WHERE clause. Since the leading column gender has only two distinct values, it really doesn't matter
if you don't specify it in the query. The database divides your composite index into two logical
subindexes, one with the key M and the other with the key F. Even though you haven't specified the
leading column gender , the database searches the two logical subindexes one after the other and gets
you the results. In other words, the database treats the query as this:
select * from sh.customers where cust_gender = 'F'
and cust_email = ' [e-mail]'
union all
select * from sh.customers WHERE cust_gender = 'M'
and cust_email = ' [e-mail]' ;
Ordering the Columns in a Composite Index
When creating a composite index, a big question is how to order the columns in the multi-column index.
Oracle recommends that you place the most commonly accessed column first in the index.
Traditionally, it was thought that you should avoid using a low cardinality column (a column with
few distinct values) as the leading column in a composite index. However, regardless of the index order,
the database can navigate straight to the leaf block containing the indexed column values because the
index leaf branch entries contain column entries based on all indexed columns.
In fact, a leading column with lower cardinality may have more advantages, as the optimizer is likely
to at least consider using an index skip scan in these cases. It has also been suggested to use the
clustering factor as a criterion when deciding which column should be the leading index column in a
composite index. The clustering factor indicates how well ordered the table's rows are in comparison to
the way the index entries are ordered in an index. For example, an arrangement that would "guarantee"
the order of the table rows to match the order of the index entries (and therefore be reflected by the
resulting clustering factor), would be if you loaded a table from a sorted set of input data in a single
action. One of the most common reasons you use a composite index is when an important query refers
to two or more columns, none of which have a high degree of selectivity. By constructing a composite
index, you increase the odds of the optimizer choosing to use that composite index, whereas it would