Database Reference
In-Depth Information
|* 5 | INDEX RANGE SCAN | I_N2 | 1 | 89 | 3 |
| 6 |
BITMAP CONVERSION FROM ROWIDS
| | 1 | 1 | 3 |
|* 7 | INDEX RANGE SCAN | I_N3 | 1 | 164 | 3 |
| 8 |
BITMAP CONVERSION FROM ROWIDS
| | 1 | 1 | 3 |
|* 9 | INDEX RANGE SCAN | I_N1 | 1 | 527 | 3 |
-----------------------------------------------------------------------------
5 - access("N2"=42)
7 - access("N3"=11)
9 - access("N1"=6)
■
Note
Bitmap plans for B-tree indexes as well as bitmap indexes are available only in enterprise edition.
Index-only Scans
A useful optimization technique related to indexes is that the database engine can not only extract lists of rowids
from indexes to access tables, it can get column data stored in the indexes. Therefore, when an index contains all the
data needed to process a query, an
index-only scan
can be executed. This is useful for reducing the number of logical
reads. In fact, an index-only scan doesn't access the table. This could be especially useful for index range scans if the
clustering factor of the index is high. The following query illustrates this. Notice that no table access is performed:
SELECT c1 FROM t WHERE c1 LIKE 'A%'
-------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 119 | 11 |
|* 1 | INDEX RANGE SCAN| I_C1 | 1 | 119 | 11 |
-------------------------------------------------------------
1 - access("C1" LIKE 'A%')
filter("C1" LIKE 'A%')
If the
SELECT
clause references the
n1
column instead of
c1
, the query optimizer isn't able to take advantage of
the index-only scan. Notice, in the following example, how the query performed 130 logical reads (11 against the
index and 119 against the table—in other words, one for each rowid get from the index) to retrieve 119 rows:
SELECT n1 FROM t WHERE c1 LIKE 'A%'
------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 119 | 130 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 119 | 130 |
|* 2 | INDEX RANGE SCAN | I_C1 | 1 | 119 | 11 |
------------------------------------------------------------------------
