Query and Join Conditions with Partitioning | VantageCloud Lake - Query Conditions and Static Row Partition Elimination - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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.

You must verify that any technique you use produces the desired results.
  • 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 specified as equality conditions.

  • Query predicates that specify equality conditions with USING variables can be effective 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, 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.

    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.
    • Use the UNION operator as a workaround.
    • Substitute constants for the USING variables in the predicate conditions.
    Instead of using parameterized queries, consider one of these alternatives.
    • Use unparameterized queries.
    • Use a preprocessor to substitute for the parameterized variables before you submit the query.