Databases Reference
In-Depth Information
Figure 7.5
Partition elimination impact on query execution plan selection.
range partitioning, accesses a single index, without any ANDing. While the index
ANDing plan on the left passes RIDs back up for ANDing prior to the fetch, in con-
trast, partition elimination skips irrelevant RIDs.
Here's another example, using a stock table:
CREATE TABLE STORE_ITEM_STOCK
( product_num INT, product_type INT, stock_num INT,
location INT, item_name VARCHAR(50))
PARTITION BY RANGE(location)
(STARTING FROM (1) ENDING AT (200) IN partitionA,
STARTING FROM (201) ENDING AT (400) IN partitionB,
STARTING FROM (401) ENDING AT (600) IN partitionC,
STARTING FROM (601) ENDING AT (800) IN partitionD)
Now consider a simple range query over this table with predicates on the location
column:
SELECT * FROM PRODUCT_STOCK WHERE location >= 250 and
location <= 382
The SQL query compiler can detect immediately that the result set includes exclu-
sively data within partition B, and only data in that partition needs to be accessed for
this query.
Similarly, partition elimination can lead to dramatic improvements in join effi-
ciency in the case of missing indexes on the join columns. Consider the following exam-
ple in Figure 7.6, joining a table of customer orders with a table of orderable products.
