Databases Reference
In-Depth Information
Generally, the optimizer prefers a full table scan under the following conditions, even in the
presence of an index on the table:
•
The table being accessed is small, consisting of few blocks.
•
A large amount of data is requested from a table.
Very few distinct values exist in an indexed column being referenced in the
WHERE
clause.
•
•
There is a high degree of parallelism defined for the table.
Of course, absence of an index on a table guarantees a full table scan, and so does the specifying of
the
FULL
hint in the query in most cases.
ROWIDs
While a full table scan requires reading all the data in a table, specifying the
ROWID
of a row is the fastest
way to retrieve a single row from a table. The
ROWID
specifies the exact location of the row and the
database goes straight to the row to fetch it. However, you rarely specify a
ROWID
directly in a query.
Instead, the database gets
ROWID
(s) from an index scan of a table's index.
Index Scans
An index on a column contains two types of data: the indexed column value and the
ROWID
of the row.
Each leaf block of a B-tree index contains the indexed data value and the corresponding
ROWID
that is
used to locate the actual row in the table. The optimizer sorts the index entries by (key,
ROWID
). An index
scan occurs whenever the database retrieves column values from an index for a specific column or
columns. If your SQL statement refers only to the indexed columns, the database retrieves the column
values from the index itself and it doesn't need to access the table. If the query refers to columns other
than the indexed column(s), the database accesses the table as well in the next step, using the
ROWID
s it
reads during the index access.
Index Unique Scan
If a query requires only a single row from a table, the database performs an index unique scan. The
database uses an index unique scan when the query contains only columns from a unique index. It also
does so when the query specifies an equality condition along with a primary key constraint.
In the following example, there's a primary key constraint on column A. Therefore, the query
guarantees that only a single row is accessed via the predicate "
where A=2000"
.
