Databases Reference
In-Depth Information
partitioning can provide the needed release to gain this efficiency and still avoid the
addressability constraints.
7.6
Partition Elimination
The major performance advantage of range partitioning comes through a technique
known through most of the industry as “partition elimination.” Partition elimination
occurs during SQL compilation time when the query compiler searches for the optimal
query execution plan for a query. The presence of range partitioning based on columns
used in query predicates provides the query optimizer a much clearer view on which
ranges are likely to be accessed as part of the query. This allows the compiler to select a
query execution plan that limits the execution to the ranges that are relevant, and can
exploit the indexing and RID architectures unique to range partitioned tables. The fol-
lowing example, courtesy of Kevin Beck at IBM, illustrates partition elimination show-
ing the impact on plan selection. The following query assumes the schema for the
industry standard TPC-H benchmark, selecting attributes from the LINEITEM table.
select l_shipdate, l_partkey, l_returnflag
from lineitem
where l_shipdate between
'01/01/1993' and '03/31/1993' and l_partkey=49981
Several query execution plans are possible for this query. Without range partition-
ing, one likely plan would exploit index ANDing, as follows:
•
Read all relevant index entries from each index.
•
Save both sets of RIDs.
•
Match them up to find which occurred in both indexes.
•
Use those to fetch the rows.
The DB2 RID architecture for range partitioned tables is that each RID in the
index contains a small identifier that indicates the specific range partition that the
record belongs to. The database can examine the range partition ID to discover if the
row might be in the desired date range, instead of reading from the l_shipdate index.
This strategy will result in half the I/O for indexes, lower memory requirements, and no
index ANDing, which will yield CPU savings as well. Figure 7.5 illustrates the differ-
ences in the query execution plans that can result from the two different physical table
designs. In this notation the triangles represent index access. Notice how the query exe-
cution plan on the left, without range partitioning, requires access to two indexes, fol-
lowed by index ANDing, while the query execution plan on the right, which assumes
