Database Reference
In-Depth Information
The queries used for the tests are the following:
SELECT sum(amount_sold) FROM sales WHERE prod_category = 'Hardware'
SELECT sum(amount_sold) FROM sales WHERE prod_category = 'Photo'
SELECT sum(amount_sold) FROM sales WHERE prod_category = 'Electronics'
SELECT sum(amount_sold) FROM sales WHERE prod_category = 'Peripherals'
SELECT sum(amount_sold) FROM sales WHERE prod_category = 'Software/Other'
SELECT sum(amount_sold) FROM sales
For each of them, the number of logical reads was measured. Figure
13-4
summarizes the results, which lead to
four main facts:
•
The number of logical reads needed to read the nonpartitioned table without an index is
independent of the selectivity. Therefore, it's efficient only when the selectivity is weak.
•
The number of logical reads needed to read a single partition of the list-partitioned table is
proportional to the selectivity, because the table has been partitioned according to the
prod_category
column. Therefore, in all situations, a minimal number of logical reads is
carried out.
•
The number of logical reads needed to read the single-table hash cluster is proportional
only to the selectivity for medium and high values. (As you'll see later, hash clusters might be
very useful when the selectivity is very strong. In this test, however, they're at a disadvantage
because of the nonuniform data distribution.)
•
The number of logical reads needed to read the table through an index is highly dependent on
the physical distribution of data. Therefore, knowing only the selectivity isn't enough to find
out whether such an access path might process data efficiently.
