Database Reference
In-Depth Information
scan may read a lot of data, this is usually inefficient. To improve the performance in such cases, you can use
index fast
full scans
. The following execution plan shows an example:
SELECT /*+
index_ffs
(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL
---------------------------------------
| Id | Operation | Name |
---------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |
INDEX FAST FULL SCAN
| T_N1_I |
---------------------------------------
1 - filter("N1" IS NOT NULL)
The peculiarity of the
INDEX FAST FULL SCAN
operation, which can be forced by the
index_ffs
hint, is that
index blocks are read from the data files with multiblock reads, as full table scans do for tables. During such a scan,
the root and the branch blocks can simply be discarded because all data is stored in the leaf blocks (usually root and
branch blocks are only a tiny fraction of the index segment, so even if they're read uselessly, the overhead is usually
negligible). As a consequence, the index structure isn't considered for the access, and therefore, the retrieved data
isn't sorted according to the index key.
The second case is similar to the previous one. The only difference is that the data has to be delivered in the same
order as it's stored in the index. For example, as shown in the following query, this is the case because an
ORDER BY
clause is specified. Because the order matters, only the
INDEX FULL SCAN
operation can be used to avoid the sort
operation:
SELECT /*+
index
(t t_n1_i) */ n1 FROM t WHERE n1 IS NOT NULL
ORDER BY n1
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 |
INDEX FULL SCAN
| T_N1_I |
-----------------------------------
1 - filter("N1" IS NOT NULL)
Because the disk I/O operations performed by the
INDEX FULL SCAN
operation are less efficient than those
performed by the
INDEX FAST FULL SCAN
operation, the former is used only when the order matters.
■
the
nls_sort
parameter affects
ORDER BY
operations. If it's set to a value different than
binary
, an index
full scan can be used for optimizing an
ORDER BY
only when either the data type of the indexed columns isn't impacted by
the nLS settings (for example, for
NUMBER
and
DATE
) or a linguistic index is used (the section “Linguistic Indexes” later in
this chapter provides information about this type of index).
Caution