Database Reference
In-Depth Information
Retrieving a Single Row
The aim of this test, which is based on the
access_structures_1.sql
script, is to compare the number of logical reads
necessary to retrieve a single row, with the following access structures in place:
•
A heap table with a primary key
•
An index-organized table
•
A single-table hash cluster that has the primary key as the cluster key
■
this chapter describes only how to take advantage of different types of segments (for example, tables, clusters,
and indexes) to minimize the number of logical reads performed during the processing of SQL statements. You can find
basic information about them in the
Oracle Database Concepts
manual, specifically in the “Schema Objects” chapter.
Note
The queries used for the tests are the following. Note that the
id
column is the primary key of the table. A row
with the value 6 exists, and the
rid
variable stores the rowid of that row:
SELECT * FROM sales WHERE id = 6
SELECT * FROM sales WHERE rowid = :rid
Because the number of logical reads depends on the height of the index, the test is performed while 10, 10,000,
and 1,000,000 rows are being stored in the table. Figure
13-3
summarizes the results. They illustrate four main facts:
•
For all access structures, a single logical read is performed through a rowid (obviously, to read
the block where the row is stored, you can't do less work than this).
•
For the heap table, at least two logical reads are necessary: one for the index and one for the
table. As the number of rows increases, the height of the index increases, and the number of
logical reads increases as well.
•
An access through the index-organized table requires one less logical read than through the
heap table.
•
For the single-table hash cluster, not only is the number of logical reads independent of the
number of rows, but in addition, it always leads to a single logical read.
