Database Reference
In-Depth Information
SELECT /*+
index_ffs
(t t_n2_i) */ n2 FROM t WHERE n2 IS NOT NULL
-----------------------------------------------
| Id | Operation | Name |
-----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | BITMAP CONVERSION TO ROWIDS | |
|* 2 |
BITMAP INDEX FAST FULL SCAN
| T_N2_I |
-----------------------------------------------
2 - filter("N2" IS NOT NULL)
SELECT /*+ index(t t_n2_i) */ n2 FROM t WHERE n2 IS NOT NULL
ORDER BY n2
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | BITMAP CONVERSION TO ROWIDS| |
|* 2 |
BITMAP INDEX FULL SCAN
| T_N2_I |
----------------------------------------------
2 - filter("N2" IS NOT NULL)
SELECT /*+ index_ffs(t t_n2_i) */
count
(n2) FROM t
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
| 2 | BITMAP CONVERSION TO ROWIDS | |
| 3 |
BITMAP INDEX FAST FULL SCAN
| T_N2_I |
------------------------------------------------
SQL Statements with Strong Selectivity
To efficiently process SQL statements with strong selectivity, the data should be accessed through a rowid, an index,
or a single-table hash cluster.
1
These three possibilities are described in the next sections.
Rowid Access
The most efficient way to access a row is to directly specify its rowid in the
WHERE
clause. However, to take advantage
of that access path, you have to get the rowid first, store it, and then reuse it for further accesses. In other words, it's a
method that can be considered only if a row is accessed at least two times. In practice, this is something that happens
quite frequently when SQL statements have strong selectivity. For example, applications used to manually maintain
data (in other words, not batch jobs) commonly access the same rows at least two times—at least once to show the
current data and at least a second time to store the modifications. In such cases, it makes sense to carefully take
advantage of the efficiency of rowid accesses.
