Database Reference
In-Depth Information
Equality Conditions and B-tree Indexes
With B-tree indexes, equality conditions are carried out with one of two operations. The first,
INDEX UNIQUE SCAN
, is
used exclusively with unique indexes. As the name suggests, at most one rowid is returned with it. The following query
is an example. The execution plan confirms, through the access predicate of operation 2, that the condition on the
id
column is applied with the
t_pk
index. Then, operation 1 uses the rowid extracted from the index to access the table.
This is carried out with the
TABLE ACCESS BY INDEX ROWID
operation. Notice that both operations are executed
only once:
SELECT /*+ index(t) */ * FROM t WHERE id = 6
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 1 |
|* 2 |
INDEX UNIQUE SCAN
| T_PK | 1 | 1 |
--------------------------------------------------------------
2 - access("ID"=6)
■
In this section, to force index scans, the
index
hint is used by specifying the name of the table only. When it's
used in this way, the query optimizer is free to choose one of the indexes from those available. In all the examples,
a single predicate is present in the
WHERE
clause. For this reason, the query optimizer always chooses the index based
on the column referenced in that predicate.
Note
The second operation,
INDEX RANGE SCAN
, is used with nonunique indexes. The only difference between this
operation and the previous one is that it can extract many rowids (527 in the example), not just one:
SELECT /*+ index_asc(t) */ * FROM t WHERE n1 = 6
--------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 527 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 527 |
|* 2 |
INDEX RANGE SCAN
| I_N1 | 1 | 527 |
--------------------------------------------------------------
2 - access("N1"=6)
From version 12.1 onward, the operation used to access a table based on rowids returned from an index range
scan is
TABLE ACCESS BY INDEX ROWID BATCHED
. The aim of this operation is to optimize the table accesses by taking
advantage of the fact that several rowids are accessed. The following example shows the execution plan for the same
query as the previous one:
SELECT /*+ index_asc(t) */ * FROM t WHERE n1 = 6
