An access plan can scan a single row partition when there are equality constraints specified on BEGIN or END bound functions in the WHERE clause of a SELECT request and a row partitioning level of the table is defined on those functions.
Rules for Performing Single-Partition Scans
The following table explains the rules for performing single-partition scans in this situation:
| WHEN a primary index is row partitioned on … | AND if the WHERE clause of a primary index retrieval from the table specifies … | THEN the database … |
|---|---|---|
| BEGIN bound function | an equality constraint on the BEGIN function | accesses only a single row partition. |
| an equality constraint on the END function | does not access only a single row partition. | |
| an equality constraint on both the BEGIN and END functions | accesses only a single row partition. | |
| END bound function | an equality constraint on the BEGIN function | does not access only a single row partition. |
| an equality constraint on the END function | accesses only a single row partition. | |
| an equality constraint on both the BEGIN and END functions | accesses only a single row partition. | |
| both a BEGIN bound function and an END bound function | an equality constraint on only the BEGIN function | does not access only a single row partition. |
| an equality constraint on only the END function | does not access only a single row partition. | |
| both the BEGIN and END functions | accesses only a single row partition. |
Examples of Single-Partition BEGIN or END Bound Function Scans on Partitioned Tables
Assume that you define the following two tables:
CREATE SET TABLE t11 ( a INTEGER, b PERIOD(DATE)) PRIMARY INDEX(a) PARTITION BY CAST ((BEGIN(b)) AS INTEGER); CREATE SET TABLE t12 ( a INTEGER, b PERIOD(DATE)) PRIMARY INDEX(a) PARTITION BY CAST((END(b)) AS INTEGER);
The relevant EXPLAIN phrase text in the reports for the examples that follow is highlighted in boldface type.
The following SELECT request uses an single-partition scan to access the rows:
EXPLAIN SELECT * FROM t11 WHERE BEGIN(b)=DATE '2005-02-03';The following shows a portion of the EXPLAIN output:
... 3) We do an all-AMPs RETRIEVE step from a single partition of df2.t11 with a condition of ("df2.t11.b = PERIOD (DATE '2005-02-03', DATE '2005-02-03'") with a residual condition of ("(BEGIN(df2.t11.b )) = DATE '2005-02-03'") into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidenceto be 1 row (56 bytes). The estimated time for this step is 0.03seconds. ...
The following SELECT request uses an all-rows, all-partitions scan to access the rows because the predicate and partitioning expression are defined on different bounds:
EXPLAIN SELECT * FROM t11 WHERE END(b)=DATE '2005-02-03';
... 3) We do an all-AMPs RETRIEVE step from df2.t11 by way of an all-rows scan with a condition of ("(END(df2.t11.b ))= DATE '2005-02-03'") into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 1 row (56 bytes). The estimated time for this step is 0.03 seconds. ...
The following SELECT request uses a single-partition scan to access the rows:
EXPLAIN SELECT * FROM t12 WHERE END(b)=DATE '2006-02-03';
... 3) We do an all-AMPs RETRIEVE step from a single partition of df2.t12 with a condition of ("df2.t12.b = PERIOD (DATE '2006-02-03'- INTERVAL '1' DAY, DATE '2006-02-03')") with a residual condition of ("(END(df2.t12.b ))= DATE '2006-02-03'") into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 1 row (56 bytes). The estimated time for this step is 0.03 seconds. ...
The following SELECT request uses a single-partition scan to access the rows:
EXPLAIN SELECT * FROM t12 WHERE a=1 AND END (b)=DATE '2006-02-03';
1) First, we do a single-AMP RETRIEVE step from a single partition of df2.t12 by way of the primary index "df2.t12.a = 1, df2.t12.b = PERIOD (DATE '2006-02-03'-INTERVAL '1' DAY, DATE '2006-02-03')" with a residual condition of ("((END(df2.t12.b )) = DATE '2006-02-03') AND (df2.t12.a = 1)") into Spool 1 (one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 1 row (56 bytes). The estimated time for this step is 0.02 seconds. ...