15.00 - Workload Characteristics, Queries, and Row Partition Elimination - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Workload Characteristics, Queries, and Row Partition Elimination

Row partition elimination is most effective in the following situations. You should always verify (using the EXPLAIN request modifier) that you are getting the desired results for any plans.

  • Row partition elimination is most effective with constant conditions on the partitioning columns.
  • When a row partitioning expression is written using something other than the RANGE_N function or a single column, row partition elimination is most effective when you specify constant equality conditions.
  • Row partition elimination can also be effective with equality conditions on USING variables if the conditions specify a single partition.
  • Row partition elimination occurs for CURRENT_DATE and DATE built-in functions for inequality conditions. This does not prevent the request from being cached.
  • Row partition elimination might occur for other built-in functions and USING variables in inequality conditions, and if it does, the action prevents the system from caching the request.
  • Multiple ORed equality conditions on the same row partitioning column do not invoke partition elimination.
  • As an alternative, you should try either to use the UNION operator on two SELECT requests or to substitute constants for the USING variables in any inequality conditions.

  • Use simple comparison of a partitioning column to a constant, built-in function, or USING variable expression for your query conditions.
  • For example.

  • d=10d >= 10 AND d <= 12
  • d BETWEEN 10 AND 12d = 10+1
  • d IN (20, 22, 24)d = 20 OR d=21
  • d = :udd BETWEEN CURRENT_DATE-7 and CURRENT_DATE-1
  • Avoid specifying query conditions with expressions or functions constructed on the row partitioning column. For example, use the form in Example 2 rather than the form in Example 1, and the form in Example 4 rather than the form in Example 3.
  • Example 1

    The predicate in this query is based on an expression constructed on the row partitioning column x:

         CREATE TABLE 
         ... 
         PARTITION BY x;
     
         SELECT ... 
         WHERE x+1 IN (2,3); 

    Example 2

    The predicate in this query is based only on the value of the row partitioning column x:

         CREATE TABLE 
         ... 
         PARTITION BY RANGE_N(x BETWEEN 1 
                                AND 65533  ← preferably use max value
                                EACH    1);  of x instead of 65,533 

    It is preferable to specify the exact upper limit of the range of x, if it is less than 65,533, in this CREATE TABLE request rather than 65,533.

         SELECT ... 
         WHERE x IN (1,2);

    Example 3

    The predicate in this query is based only on the value of row partitioning column x even though the table is partitioned by the expression x + 1:

         CREATE TABLE 
         ... 
         PARTITION BY x+1;
     
         SELECT ... 
         WHERE x IN (1,2);

    Example 4

    The predicate in this query is based only on the value of row partitioning column x:

         CREATE TABLE 
         ... 
         PARTITION BY RANGE_N(x BETWEEN 0 
                                AND 65532   preferably use max value
                                EACH    1);   of x instead of 65532

    Note that it is preferable to specify the exact upper limit of the range of x, if it is less than 65,552, in this CREATE TABLE request rather than 65,532.

         SELECT ... 
         WHERE x IN (1,2);