Workload Characteristics, Queries, and Row Partition Elimination - Teradata VantageCloud Lake

Lake - Database Reference
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
ohi1683672393549.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
ohi1683672393549
Row partition elimination is most effective in the following situations. Use the EXPLAIN request modifier to verify 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 may occur for other built-in functions and USING variables in inequality conditions, and if so, 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, 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
d 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 
                       EACH    1);  
If the maximum value of x is less than 65,533, use the maximum value rather than 65,533.
SELECT ...
WHERE x IN (1,2);

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   
                       EACH    1);  
If the maximum value of x is less than 65,532, use the maximum value rather than 65,532.
SELECT ...
WHERE x IN (1,2);