The intent of the following guidelines is to indicate what to do, and what not to do, to maximize static row partition elimination to help optimize your queries.
- Avoiding specifying expressions or functions (excluding date-based built-in functions) on the partitioning column of a partitioned table.
The following examples show problematic table definitions and queries with rewritten definitions and queries for improved partitioning.
Original Non-Optimal Table Definition and Queries Rewritten Table Definitions and Queries
CREATE TABLE ... PARTITION BY x; SELECT ... WHERE x+1 IN (2,3);
CREATE TABLE ... PARTITION BY RANGE_N(x BETWEEN 1 AND 65533 EACH 1);
You can specify the maximum partition number anticipated for this 2-byte partitioning rather than 65,533 in this partition specification.
SELECT ... WHERE x IN (1,2);
CREATE TABLE ... PARTITION BY x+1; SELECT ... WHERE x IN (1,2);
CREATE TABLE ... PARTITION BY RANGE_N(x BETWEEN 0 AND 65532 EACH 1);
You can specify the maximum partition number anticipated for this 2-byte partitioning rather than 65,532 in this partition specification.
SELECT ... WHERE x IN (1,2);
- Query predicates are optimally effective when you define the expression with constant conditions on the partitioning columns for the partitioned table.The following predicates are all good examples of simple equality comparisons with constant conditions, where d is the partitioning column for the table.
- d = 10
- d ≥ 10 AND d ≤ 12
- d BETWEEN 10 AND 12
- d = 10+1
- d IN (20, 22, 24)
d = 20 OR d=21
If the partitioning expression for the table is not defined using the RANGE_N function, query predicates are optimally effective when you specify them as equality conditions.
- Query predicates can be effective when they specify equality conditions with USING variables if the predicate specifies a single partition or the DATE or CURRENT_DATE built-in functions. For example, if the partitioning expression is date-based and the predicated condition on the partitioning column is defined using the CURRENT_DATE built-in function, then the Optimizer does static row partition elimination.
The Optimizer can also substitute USING request modifier values to further optimize the query plan. See Request Parsing in Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.
Multiple OR equality conditions on the same partitioning column do not permit row partition elimination.If this is the case when you specify equality conditions on USING variables, consider either of the following alternatives.
Instead of using parameterized queries, consider one of these alternatives.
- Use the UNION operator as a workaround.
- Substitute constants for the USING variables in the predicate conditions.
- Use unparameterized queries.
- Use a preprocessor to substitute for the parameterized variables before you submit the query.