Databases Reference
In-Depth Information
probably have bypassed any indexes you created separately on the two columns, both of which have a
very low selectivity.
Note
Selectivity is a computation based on column statistics (particularly on the number of distinct values and
high/low values). The optimizer computes selectivity for multiple columns in an “ANDed” predicate by computing
the individual selectivity (1/number of distinct values) for each column and then multiplying those results together
to get the overall selectivity. For example, if you have
WHERE GENDER = 'F' AND STATUS = 'ACTIVE
' where both
gender and status have only 2 distinct values, each column will have a selectivity of .5. The total predicate
selectivity is then .5 * .5 or .25. If a composite index exists that has both columns, the optimizer will compute
index selectivity using the .25 combined predicate selectivity along with the index stats (like the clustering factor)
to make its final cost calculation for using the index.
A big advantage of using a composite index is that if all the columns required by a query are in the
composite index itself, the database returns the values of the columns from the index without
subsequently having to access the table. Thus, you'll see a reduced I/O when using composite indexes in
most cases since you're avoiding the scan of the table itself using the
ROWID
s, as is the case when you use
an index on a single column.
A key criterion in deciding how to order keys in a composite index is to ensure that the leading
portion of the index consists of keys used in
WHERE
clauses. If some of the keys are often specified in
WHERE
clauses, make sure that these keys make up the leading portion of the index. This ensures that queries
that specify only these keys will use the index.
Choosing Keys for Composite Indexes
You can create a composite index with its columns in any order you want, but your goal should be to
create a composite index only when the different keys appear frequently together in an application's
WHERE
clauses and you're currently using an
AND
operator to combine the columns. A composite index
would be a better choice in this case. The one thing you must do is get a rough estimate of the selectivity
of the columns you want to include in a composite index. If the combined selectivity of the columns is
better than the individual selectivity of the columns, the composite index will be beneficial. You can also
consider a composite index in cases where key queries do a select of the same set of keys based on
multiple key values. Simply create a composite index with all the keys for better performance.
Let's use a couple of simple examples to drive home our point as to how a composite index will
benefit you by reducing I/O when all the columns required to satisfy a query are in the index itself. In the
first example, you create a single column index and check the explain plan.
SQL> create table test_tab
2 (a number, b varchar2(10), c varchar2(10));
Table created.
SQL> create index single_idx1 on test_tab (a);
