Database Reference
In-Depth Information
1
2
A
B
3
4
_
Sort and merge
pointer sets
_
_
_
TX
_
5
Figure 10.1
Two thin indexes and
multiple index access.
Sort result rows
Let us consider how the indexes shown in Figure 10.1 could be used to
satisfy SQL 10.1.
Index ANDing
was important in the days when fat indexes were not very
common because of the high cost of disk space. Its introduction had provided
an alternative on those occasions when the optimizer had been forced to make
a choice between index A and index B, followed by reads to the table rows to
evaluate the other predicate. This could imply heavy access to the table.
It would, of course, be more efficient to do the following (the numbers refer
to those in Fig. 10.1):
ž
Collect all the pointers for those index rows that satisfy the corresponding
predicate, from both index slices (1) and (2).
ž
Sort the two sets of pointers into page number sequence (3).
ž
Merge the two sorted sets of pointers (3).
ž
Access the table (4)
only
for those rows that satisfy
both
WHERE
clauses—one table touch per result row; these table touches will be
faster
than they would have been with random reads because, as the pointers
have been sorted by page number, the scan of the table pages will be done
using skip-sequential read.
How would this access path compare with that for a single composite index
(A, B)? The latter would be preferable to the multiple index access because
the number of index touches would be less. The optimizer may choose skip-
sequential read anyway if it appears to be a good idea. A fat index (A, B, C) is
naturally the most efficient solution for this query.
Index ANDing is not a common access path in operational applications today.
If one is detected with EXPLAIN, consideration should be given to eliminating
it by using a fat index because index ANDing, using the mechanism described
above, has three
severe pitfalls
:
1.
The number of sequential touches may be excessive when a simple pred-
icate has a high filter factor.
Search WWH ::
Custom Search