15.00 - Considerations for Basing the Partitioning Expression on a RANGE_N Function - Teradata Database

Teradata Database Design

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

Considerations for Basing the Partitioning Expression on a RANGE_N Function

Using the RANGE_N function to build a partitioning expression offers the following advantages:

  • Defining an efficient mapping or ranges between integer (BYTEINT, SMALLINT, INTEGER, BIGINT), character (CHARACTER, GRAPHIC, VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC), DATE, or TIMESTAMP type and INTEGER numbers.
  • Provides more opportunities than other expressions to optimize queries.
  • The Optimizer knows the number of defined partitions when you specify RANGE_N to define the partitioning expression.
  • For other partitioning expressions, the Optimizer generally assumes a total of 65,535 partitions when statistics have not been collected, which could easily be far more than the number of populated partitions. However, collecting statistics on PARTITION can provide information about which partitions are empty.

  • Faster partitioning changes than any other expression using the following ALTER TABLE options.
  • ADD RANGE
  • DROP RANGE
  • You can optimize the effects of using RANGE_N in your partitioning expression by observing the following guidelines:

  • Reference only a single integer (BYTEINT, SMALLINT, INTEGER, BIGINT), character (CHARACTER, GRAPHIC, VARCHAR, VARCHAR(n) CHARACTER SET GRAPHIC), DATE, or TIMESTAMP column, not expressions.
  • For example, specifying a simple expression such as in place of a column name in the RANGE_N specification, even if the expression references only a single column, can hinder, or even prevent, row partition elimination.

  • For equal‑sized ranges, always specify an EACH clause.
  • Note the following collateral facts about equal‑ and unequal‑sized partitions:

  • The performance of unequal‑sized partitions varies depending on which partitions are accessed.
  • Unequal size ranges can prevent fast partitioning changes from being made using the ALTER TABLE statement (see SQL Data Definition Language).
  • Using the NO RANGE, NO RANGE OR UNKNOWN, or UNKNOWN specifications for a range can negatively affect later ALTER TABLE partitioning strategies.
  • Do not use these clauses unless you have specific reasons for doing so (see SQL Functions, Operators, Expressions, and Predicates for details).

    Reasons not to use the NO RANGE, NO RANGE OR UNKNOWN, and UNKNOWN clauses include the following.

  • The maintenance and use of partitioned tables is simpler if you do not use these options, and avoiding their use also prevents bad data from being inserted into the table.
  • If these partitions are not eliminated by row partition elimination, they can cause negative performance impacts if they contain a large of number of rows.
  • If a partitioning column is NOT NULL such that test values can never be null, do not specify UNKNOWN or NO RANGE OR UNKNOWN.
  • If the specified ranges cover all possible values, do not specify NO RANGE or NO RANGE OR UNKNOWN.