Query and Join Conditions with Partitioning | CREATE TABLE | Teradata Vantage - Query Conditions and Static Row Partition Elimination - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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.

As always, you need to 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 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.
    • 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.