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
 
Search WWH ::




Custom Search