Database Reference
In-Depth Information
■
In this section, several hints are used to force index scans by specifying the name of the table and on which
columns the index has to be created. the advantage of this syntax compared to the method where the index name is
specified is that the hint doesn't depend on the index name. this gives greater robustness to the hint. Its disadvantage is
that the hint doesn't guarantee that the query optimizer always selects the same index.
Note
As mentioned in the previous section, the index scan is performed in ascending order per default. This means
that when an
ORDER BY
using binary comparisons (later on, the “Linguistic Indexes” section provides information
about the different types of comparison and how to deal with them) is applied to the same column as the range
condition, the result set is already sorted. As a result, no explicit sort is carried out. However, when the
ORDER BY
is
required in descending order, an explicit sort needs to be executed, as the following query illustrates. The sort is carried
out by operation 1,
SORT ORDER BY
. Notice how the index scan in ascending order is forced by the
index_asc
hint:
SELECT /*+
index_asc
(t (t.id)) */ * FROM t WHERE id BETWEEN 6 AND 19 ORDER BY id
DESC
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 |
SORT ORDER BY
| | 1 | 14 |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 |
|* 3 | INDEX RANGE SCAN | T_PK | 1 | 14 |
---------------------------------------------------------------
3 - access("ID">=6 AND "ID"<=19)
The same query can naturally take advantage of a descending index scan to avoid the explicit sort. Here's an
example, where the
SORT ORDER BY
operation is no longer present in the execution plan:
SELECT /*+
index_desc
(t (t.id)) */ * FROM t WHERE id BETWEEN 6 AND 19 ORDER BY id
DESC
------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 14 |
| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 15 | 14 |
|* 2 | INDEX RANGE SCAN
DESCENDING
| T_PK | 1 | 15 | 14 |
------------------------------------------------------------------------
2 - access("ID"<=19 AND "ID">=6)
