Database Reference
In-Depth Information
In this type of situation, in order to take advantage of index-only scans, you might add columns to an index even
if they aren't used to apply a restriction. The idea is to create a composite index with an index key that's composed
of all columns that are referenced in the SQL statement (also known as
covering index
), not only those in the
WHERE
clause. In other words, you “misuse” the index to store redundant data and, therefore, minimize the number of logical
reads. Note, however, that the leading column of the index must be one of the columns referenced in the
WHERE
clause.
In this specific case, this means that a composite index on the
c1
and
n1
columns is created. With that index in place,
the very same query retrieves the same rows with only 10 logical reads instead of 130:
SELECT n1 FROM t WHERE c1 LIKE 'A%'
---------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 119 | 10 |
|* 1 | INDEX RANGE SCAN| I_C1N1 | 1 | 119 | 10 |
---------------------------------------------------------------
1 - access("C1" LIKE 'A%')
filter("C1" LIKE 'A%')
■
For list partitioned tables, the query optimizer generates an execution plan based on an index-only scan to
resolve an
IN
condition only when the partition key is part of the index. the
index_only_scan_list_part.sql
script
provides an example. For range and hash partitioned tables, this restriction doesn't exist.
Caution
Even though the examples in this section are based on B-tree indexes, index-only scans are available for bitmap
indexes as well.
Index-organized Tables
One particular way to achieve an index-only scan is to create an index-organized table. The central idea of this kind
of table is, in fact, to avoid having a table segment at all. Instead, all data is stored in an index segment based on the
primary key. It's also possible to store part of the data in an overflow segment. By doing so, however, the benefit of
using an index-organized table vanishes (unless the overflow segment is rarely accessed). The same happens when a
secondary index
(that is, another index in addition to the primary key) is created: two segments need to be accessed.
Hence, there's no benefit in using it. For these reasons, you should consider using index-organized tables only when
two requirements are met. First, the table is normally accessed through the primary key. Second, all data can be
stored in the index structure (a row can take at most 50 percent of a block). In all other cases, it makes little sense to
use them.
A row in an index-organized table isn't referenced by a
physical rowid
. Instead, it's referenced by a
logical rowid
.
This kind of rowid is composed of two parts: first, a guess referencing the block that contains the row (key) at the
time it was inserted, and second, the value of the primary key. A visit to the index-organized table by logical rowid at
first follows the guess, hoping to find the row still in the insert-time block, but because the guess isn't updated when
block splits occurs, it might become stale when
INSERT
and
UPDATE
statements are executed. If the guess is correct,
with a logical rowid, it's possible to access one row with a single logical read. In case the guess is wrong, the number
of logical reads would be equal to or greater than two (one for the useless access through the guess, plus the regular
access with the primary key). Naturally, to have the best performance, it's capital to have correct guesses. To assess
the correctness of such guesses, the
pct_direct_access
column, which is updated by the
dbms_stats
package, is
