Database Reference
In-Depth Information
COLUMN_NAME NUM_DISTINCT
----------- ------------
ID 10000
N1 18
N2 112
N3 60
In such situations, it's more efficient to apply the various conditions with a single index built on several columns.
For example, the following execution plan shows what happens if a composite index is created on the three columns.
It's essential to understand that with this index, the number of logical reads (4) is lower because the index scan returns
only rows that fulfill the whole
WHERE
clause (in this case, one row):
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
4
|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 | 4 |
|* 2 | INDEX RANGE SCAN |
I_N123
| 1 |
1
| 3 |
--------------------------------------------------------------------------
2 - access("N1"=6 AND "N2"=42 AND "N3"=11)
At this point, it's crucial to recognize that the database engine is able to carry out an index range scan even
when not all columns on which the index is built are referenced in the
WHERE
clause. The basic requirement is that a
condition should be applied to the leading column of the index key. For example, with the
i_n123
index used in the
previous example, the conditions on the columns
n2
and
n3
are optional. The following query shows an example
where no condition on the
n2
column is present:
SELECT * FROM t WHERE n1 = 6 AND n3 = 11
--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 12 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 8 | 12 |
|* 2 | INDEX RANGE SCAN | I_N123 | 1 | 8 | 4 |
--------------------------------------------------------------------------
2 - access("N1"=6 AND "N3"=11)
filter("N3"=11)
Be aware that in the previous execution plan, all index keys fulfilling the
n1 = 6
predicate must be accessed, even
though the
n3 = 11
predicate is mentioned in the access predicate. On the one hand, that approach is suboptimal
because unnecessary parts of the index are accessed. On the other hand, it's much better to apply the
n3 = 11
predicate as a filter during the index scan than when the table is accessed, as in the example provided before. In any
case, for best performance, the predicates should be applied to the leading columns of the index.
There are cases where the index can even be used (efficiently) when there's no condition on the leading column
of the index key. Such an operation is called an
index skip scan
. However, using it makes sense only when the leading
column has a very low number of distinct values, because an independent index range scan is performed for each
