Database Reference
In-Depth Information
Figure 13-3.
Different access structures lead to different numbers of logical reads
In summary, for retrieving a single row, a “regular” table with an index is the least efficient access structure.
However, as I describe later in this chapter, “regular” tables are the most commonly used because you can take
advantage of the other access structures only in specific situations.
Retrieving Thousands of Rows
The purpose of this test, which is based on the
access_structures_1000.sql
script, is to compare the number of
logical reads necessary to retrieve thousands of rows with the following access structures in place:
•
Nonpartitioned table without an index.
prod_category
column is the partition key.
•
List-partitioned table. The
prod_category
column is the cluster key.
•
Single-table hash cluster. The
prod_category
column. For this test, two different
physical distributions of the rows in the table segment (and hence, different clustering factors)
were tested.
•
Nonpartitioned table with an index on the
The test data set consists of 918,843 rows. The following query shows the distribution of the values for the
prod_category
column:
SQL> SELECT prod_category, count(*), ratio_to_report(count(*)) over() AS selectivity
2 FROM sales
3 GROUP BY prod_category
4 ORDER BY count(*);
PROD_CATEGORY COUNT(*) SELECTIVITY
-------------- ---------- -----------
Hardware 15357 .017
Photo 95509 .104
Electronics 116267 .127
Peripherals 286369 .312
Software/Other 405341 .441
