Static Row Partition Elimination | Optimizer Process | Teradata Vantage - Static Row Partition Elimination - Advanced SQL Engine - Teradata Database

About Static Row Partition Elimination

Static row partition elimination, especially when applied at multiple levels, can significantly reduce the number of data blocks the database must read and process to respond to a query. Static row partition elimination is based on constant conditions on the partitioning columns and system-derived columns PARTITION and PARTITION#L n. The term constant conditions refers to literal values and predicate conditions such as equality, inequality, and BETWEEN and the substitution of any CURRENT_TIME, CURRENT_TIMESTAMP, or USING values for a specific plan and substitution of any DATE, CURRENT_DATE, TEMPORAL_DATE, or USER values.

The database applies static row partition elimination to each level independently, and combines the result into a single row partition elimination list. If the system cannot eliminate any row partitions at a level, then it must process all the row partitions for that level. The same algorithms as used for single-level row partitioning are used at each level. The exception to this is conditions on system-derived column PARTITION#L n, which the row partition elimination algorithms consider for level n instead of conditions on system-derived column PARTITION. The system considers conditions on both the system-derived column PARTITION and the system-derived column PARTITION#L1 for single-level row partitioning.

Static partition elimination for a table or join index that has 8-byte partitioning is limited to 8,000 partitions, and no further static partition elimination occurs for that table or join index. The database applies static partition elimination to the first partitioning level, then the second partitioning level, and so on until either partition elimination completes or the limit of 8,000 non-eliminated partitions is reached. If the limit is reached, the remaining partitions in that level and any lower levels are not eliminated.

If there are constant equality constraints on the partitioning columns of a partitioning expression, static row partition elimination occurs. All but the specified row partition for that partitioning expression is eliminated.

The Optimizer might not always be able to eliminate as many row partitions as is theoretically possible because of limitations in its ability to detect such opportunities.

You should always run EXPLAIN request modifiers and measure actual system performance to verify that the desired partition elimination and performance occur for a candidate partitioning scheme and workload. If the results of your verification tests are not acceptable, or if they are not at the performance levels you expected, you should consider using a different partitioning scheme, or even using no partitioning, and then make other physical database design changes or query modifications such as adding constant conditions on the partitioning columns to obtain the level of performance you need and want.

Static Row Partition Elimination for RANGE_N Partitioning Expressions

Note that this list does not include all possible non-decreasing linear expressions. Any such expressions that are not listed are not recognized for static row partition elimination by the database.

Static Row Partition Elimination for CASE_N Row Partitioning Expressions

Static row partition elimination can occur for a CASE_N row partitioning expression if the Optimizer recognizes that conditions in the CASE_N row partitioning expression are not satisfiable in relation to the specified query conditions.

Static Row Partition Elimination for Character Partitioning Expressions

There are special considerations for row partition elimination for RANGE_N character partitioning.

An example is the case where the RANGE_N condition specifies ranges that do not specify a range end, and there is a term of the form part_col > range_end, where range_end is the string equal to the next range beginning, except it substitutes the character with the next lowest code in the last character position as is done for the partitioning expression of t1 in the following example:

CREATE TABLE t1(
  i INTEGER,
  j CHARACTER(4),
  k INTEGER)
PRIMARY INDEX(i)
PARTITION BY (RANGE_N(j BETWEEN 'aaaa','cccc','eeee','gggg',                                 
                                'iiii','kkkk','mmmm','oooo',
                                'qqqq','ssss'
                        AND     'tttt',
              NO RANGE));

The following SELECT request reads from 9 row partitions even though only 8 row partitions need to be read to return the response set. This happens because the range end of 'eeed' for row partition 2 is implied rather than explicitly specified in the RANGE_N expression.

SELECT *
FROM t1
WHERE j > 'eeed';

For INTEGER and DATE data, the Optimizer can subtract 1 (or 1 day) from the next range start to find the range end, but it cannot determine the next lowest or highest code point in a character set based on a given collation sequence. As a result, row partition elimination always finds one extra row partition that needs to be read in such cases.

Row partition elimination is supported on LIKE predicates of the form part_col LIKE 'abc%' for RANGE_N partitioning, where 'abc' represents any string literal. The '%' metacharacter, which matches any string of 0 or more characters if present in the search string, might only exist as the last character in the string (depending on the server character set, this might not be ASCII '%', but a different code point such as full width percent: _unicode 'FF05'XC). Use of the '_' metacharacter to match any single character results in no row partition elimination from the containing predicate.

Note that row partition elimination is not guaranteed for LIKE predicates with CASE_N character partitioning. This is because row partition elimination with CASE_N is dependent on SAT-TC support of LIKE predicates.

The best practice is to define a character partitioning using a RANGE_N partitioning expression if you expect the Optimizer to eliminate row partitions when LIKE predicates are specified in a query. Row partition elimination should occur with CASE_N character partitioning, similar to non-character partitioning, when other comparison operators such as =, <, >, <=, >=, <>, BETWEEN are specified in the CASE_N expression and WHERE clause predicate.

Row partition elimination requires that to be able to evaluate predicates, translation of partitioning column data is not required for the execution of that predicate.

If there is no valid translation from the character set of a constant expression in the predicate to the character set of the partitioning column in the comparison, then the database converts both to Unicode. When this occurs, no row partition elimination occurs.

To increase the likelihood of row partition elimination, you should ensure that the partitioning columns of a character-partitioned table be stored using the Unicode server character set.

A CAST of character data can truncate the string, resulting in a loss of information.

The database does not support row partition elimination for RANGE_N partitioning when the test value involves a CAST or other data conversion operation unless the predicate qualifying the level for row partition elimination is an equality condition.

Static row partition elimination for RANGE_N partitioning requires that none of the ranges be unbounded, meaning specified by an asterisk character for the start of the first range or the end of the last range. This restriction also applies to static row partition elimination for a character RANGE_N partitioning level. There is an exception to this restriction if the WHERE clause predicate qualifying the level for row partition elimination is an equality condition, and static row partition elimination should occur at that level.

For example, consider the following character partitioning definition:

CREATE MULTISET TABLE df2.t4, NO FALLBACK, NO BEFORE JOURNAL,
                              NO AFTER JOURNAL, CHECKSUM=DEFAULT (
  a INTEGER,
  b CHARACTER(4) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX (a)
PARTITION BY RANGE_N(b BETWEEN 'A' AND 'F','FA' AND 'Z');

The following SELECT request reads from only one row partition because it specifies the concatenation operator and the SOUNDEX function in its WHERE clause:

SELECT *
FROM t4
WHERE b BETWEEN 'a'||'a' AND SOUNDEX('d');

Specifying a string function on a partitioning column within a predicate, however, eliminates the possibility of its use for row partition elimination. For example, consider the following SELECT request against table t4. Because the request specifies the TRIM function on column b, which is the character partitioning column for t4, the Optimizer does not apply static row partition elimination to return the result set.

SELECT *
FROM t4
WHERE TRIM(trailing ' ' FROM b) BETWEEN 'a' AND 'd';

The database handles requests issued in ANSI session mode differently than requests submitted in Teradata session mode.

In ANSI session mode, the database handles any single-table conditions between a NOT CASESPECIFIC column and a constant with no case sensitivity qualifier by internally converting the column reference to be CASESPECIFIC. This disqualifies the term from being used in row partition elimination; therefore, if you want the Optimizer to apply row partition elimination to requests in ANSI session mode, you should create the character partitioning columns as CASESPECIFIC. Alternatively, you can cast all of the constants in the WHERE clause predicate of a query to be NOT CASESPECIFIC.

For example, consider the following table definition:

CREATE MULTISET TABLE MWS2.t4, NO FALLBACK, NO BEFORE JOURNAL,
                               NO AFTER JOURNAL, CHECKSUM=DEFAULT (
  a INTEGER,
  b CHARACTER(4) CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX (a)
PARTITION BY RANGE_N(b BETWEEN 'A' AND 'F',
                              'FA' AND 'Z');

In ANSI session mode, the database reads from all row partitions (does not apply static row partition elimination) because of the implicit CAST on column b.

SELECT *
FROM t4
WHERE b BETWEEN 'a' and 'd' ;

After you modify the same request slightly, the database reads from only one row partition because of the explicit CAST of column b.

SELECT *
FROM t4
WHERE b BETWEEN CAST ('a' AS CHARACTER(4) NOT CASESPECIFIC)
        AND     CAST ('d' AS CHARACTER(4) NOT CASESPECIFIC);

The following examples show the behavior of SELECT requests against character partitioning columns in Teradata session mode. First, create the following table:

CREATE TABLE markets (
  productname     VARCHAR(50) NOT CASESPECIFIC,
  region          BYTEINT NOT NULL,
  activity_date   DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  revenue_code    BYTEINT NOT NULL,
  business_sector BYTEINT NOT NULL,
  note            VARCHAR(256))
PRIMARY INDEX (productname, region)
PARTITION BY (RANGE_N(productname BETWEEN 'A','B','C','D','E','F',
                                          'G','H','I','J','K','L',
                                          'M','N','O','P','Q','R',
                                          'S','T','U','V','W','X',                                           'Y', 'Z' AND
             'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',
             NO RANGE,
             UNKNOWN));

The comments associated with the following queries assume an even distribution of the rows among the row partitions and many data blocks per partition. Without being able to use static row partition elimination, these queries would be all-AMP, full-table scans.

The first SELECT request reads 2 row partitions on all AMPs. This means that the database reads roughly 2/28 of the data blocks in the markets table.

SELECT *
FROM markets
WHERE productname LIKE 'B%';

The next SELECT request reads only one row partition on all AMPS; therefore, the database reads roughly 1/28 of the data blocks in markets.

SELECT *
FROM markets
WHERE productname < 'B';

The next SELECT request reads 2 row partitions on all AMPs; therefore, the database reads roughly 2/28 of the data blocks in markets.

     SELECT *
     FROM markets
     WHERE productname LIKE UPPER('B%');

The following example uses the KANJIEUC_0U session character set and JIS_COLL collation.

CREATE TABLE t12 (
  a INTEGER,
  b VARCHAR(15) CHARACTER SET GRAPHIC)
PRIMARY INDEX(a)
PARTITION BY (RANGE_N(b BETWEEN _GRAPHIC '9758'XC
                        AND     _GRAPHIC '9759'XC,
              NO RANGE, UNKNOWN));

The following SELECT request reads one row partition on all AMPs:

SELECT *
FROM t12
WHERE b BETWEEN _GRAPHIC '9758'XC AND _GRAPHIC '9759'XC;