Efficient queries for tables with data-partitioned secondary indexes
The number of partitions that Db2 accesses to evaluate a query predicate can affect the performance of the query. A query that provides data retrieval through a data-partitioned secondary index (DPSI) might access some or all partitions of the DPSI.
For a query that is based
only on a DPSI key value or range, Db2 must
examine all partitions. However, if the query also has predicates
on the leading columns of the partitioning key, Db2 does
not need to examine all partitions. The removal from consideration
of inapplicable partitions is known as page range screening,
which is also sometimes known called limited partition scan.
The use of page range screening scan can be determined at bind time or at run time. For example, page range screening can be determined at bind time for a predicate in which a column is compared to a constant. Page range screening occurs at run time if the column is compared to a host variable, parameter marker, or special register.
Example: page range screening
The following example demonstrates how you can use a partitioning index to enable page range screening on a set of partitions that Db2 needs to examine to satisfy a query predicate.
Suppose that you create table Q1, with partitioning index DATE_IX and DPSI STATE_IX:
CREATE TABLESPACE TS1 NUMPARTS 3;
CREATE TABLE Q1 (DATE DATE,
CUSTNO CHAR(5),
STATE CHAR(2),
PURCH_AMT DECIMAL(9,2))
IN TS1
PARTITION BY (DATE)
(PARTITION 1 ENDING AT ('2002-1-31'),
PARTITION 2 ENDING AT ('2002-2-28'),
PARTITION 3 ENDING AT ('2002-3-31'));
CREATE INDEX DATE_IX ON Q1 (DATE) PARTITIONED CLUSTER;
CREATE INDEX STATE_IX ON Q1 (STATE) PARTITIONED;Now suppose that you want to execute the following query against table Q1:
SELECT CUSTNO, PURCH_AMT
FROM Q1
WHERE STATE = 'CA';Because the predicate is based only on values of a DPSI key (STATE), Db2 must examine all partitions to find the matching rows.
Now suppose that you modify the query in the following way:
SELECT CUSTNO, PURCH_AMT
FROM Q1
WHERE DATE BETWEEN '2002-01-01' AND '2002-01-31' AND
STATE = 'CA';Because the predicate is now based on values of a partitioning index key (DATE) and on values of a DPSI key (STATE), Db2 can eliminate the scanning of data partitions 2 and 3, which do not satisfy the query for the partitioning key. This can be determined at bind time because the columns of the predicate are compared to constants.
Now suppose that you use host variables instead of constants in the same query:
SELECT CUSTNO, PURCH_AMT
FROM Q1
WHERE DATE BETWEEN :hv1 AND :hv2 AND
STATE = :hv3;Db2 can use the predicate on the partitioning column to eliminate the scanning of unneeded partitions at run time.
Example: page range screening when correlation exists
Writing queries to take advantage of page range screening is especially useful when a correlation exists between columns that are in a partitioning index and columns that are in a DPSI.
For example, suppose that you create table Q2, with partitioning index DATE_IX and DPSI ORDERNO_IX:
CREATE TABLESPACE TS2 NUMPARTS 3;
CREATE TABLE Q2 (DATE DATE,
ORDERNO CHAR(8),
STATE CHAR(2),
PURCH_AMT DECIMAL(9,2))
IN TS2
PARTITION BY (DATE)
(PARTITION 1 ENDING AT ('2004-12-31'),
PARTITION 2 ENDING AT ('2005-12-31'),
PARTITION 3 ENDING AT ('2006-12-31'));
CREATE INDEX DATE_IX ON Q2 (DATE) PARTITIONED CLUSTER;
CREATE INDEX ORDERNO_IX ON Q2 (ORDERNO) PARTITIONED;Also suppose that the first 4 bytes of each ORDERNO column value represent the four-digit year in which the order is placed. This means that the DATE column and the ORDERNO column are correlated.
To take advantage of page range screening, when you write a query that has the ORDERNO column in the predicate, also include the DATE column in the predicate. The partitioning index on DATE lets Db2 eliminate the scanning of partitions that are not needed to satisfy the query. For example:
SELECT ORDERNO, PURCH_AMT
FROM Q2
WHERE ORDERNO BETWEEN '2005AAAA' AND '2005ZZZZ' AND
DATE BETWEEN '2005-01-01' AND '2005-12-31';