15.00 - Query Conditions and Static Row Partition Elimination - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Query Conditions and Static Row Partition Elimination

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 against them with both the definition and the query rewritten to make better use of 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);

    It would be preferable to 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);
     

    Note that it would be preferable to 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 them 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.
  • Depending on the particular situation, the Optimizer might also have the opportunity to substitute USING request modifier values to further optimize the query plan (see “Chapter 1: Request Parsing” in SQL Request and Transaction Processing for details).

    Note that 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.