Databases Reference
In-Depth Information
Composite Indexes
You can create an index on multiple columns in a table. If you want to create an index on the
EMPLOYEE_ID
and
DEPARTMENT_ID
columns in the employees table, for example, you can do so, and the
result is called a composite or concatenated index. Here's an example:
SQL> create index test_idx1 on employees(employee_id,department_id);
Index created.
SQL>
You can create composite B-tree indexes as well bitmap indexes. The optimizer will take into
account a composite index when the
WHERE
clause in a query refers to all the columns in the index or
even the leading column. The previous example showed a composite index with just two columns, but
you can have even more columns if you wish. The following example shows a composite index created
by using three columns (
LAST_NAME, JOB_ID
, and
SALARY
) from the employees table in the HR schema.
You do this when you have an application that frequently refers to these three columns.
SQL> create index employees_idx1
2* on employees (last_name,job_id,salary);
Index created.
SQL>
Once you create this index, any query that refers to the
LAST_NAME
column, or the
LAST_NAME
and
JOB_ID
columns, or all three columns is likely to cause the optimizer to use the index. A query that
doesn't include the leading column in a composite index (
LAST_NAME
, in this example) will ignore the
index. At least, this was the traditional behavior. The introduction of index skip scans changes this
default behavior.
Understanding Index Skip Scans and Composite Indexes
In earlier releases of the Oracle Database, a SQL statement used a composite index only if the
statement's constructs used a leading portion of the index. The leading portion of an index is one or
more columns in the index that are specified first in the list of columns. For example, say you have the
following composite index:
create index mycomp_idx
on table mytable(a,b,c);
In this index, a, ab, and abc are all considered leading portions of an index. The column or column
combinations b, c, and bc aren't considering leading portions. However, the introduction of the index
skip scan feature (in the Oracle 9i release) has changed this behavior. An index skip scan eliminates or
skips through a composite index by using logical subindexes. Logical subindexes mean just that: you
don't create those indexes. The skip scanning feature assumes that the composite index is indeed
composed of multiple subindexes. When does the database perform a skip scan? It may do so when your
query predicate doesn't specify the leading column of a composite index.
