Rules for Altering the Row Partitioning for Character Partitioning - Advanced SQL Engine - Teradata Database

See also Partitioning Expressions Using on a CASE_N or RANGE_N Character Column.

The following rules and restrictions apply in addition to all other rules for using ALTER TABLE requests to modify row-partitioned tables:

You can use ALTER TABLE requests to modify character partitioning only as described below.

PARTITION BY Expression on a Table Not Populated with Rows

The table is not populated with rows and the partitioning is changed by specifying a new PARTITION BY expression.

Partitioning Levels that Do Not Specify Character Data Comparisons

The character partitioning is multilevel, with at least one, and possibly several, partitioning levels that do not specify character data comparisons.

You can alter those non-character expression partitioning levels that consist only of a RANGE_N function using the ADD RANGE or DROP RANGE options in the same way as you would for a non-character partitioning.

The session collation must be the same as the table collation, and the session mode must also be the same as was in effect when the table or join index was created.

For this example, suppose you have created the following row-partitioned orders table in Teradata session mode in database df2 with ASCII collation.

CREATE SET TABLE df2.orders, NO FALLBACK, NO BEFORE JOURNAL,
    NO AFTER JOURNAL, CHECKSUM = DEFAULT (
  o_orderkey      INTEGER NOT NULL,
  o_custkey       INTEGER,
  o_orderstatus   CHARACTER(1)
                  CHARACTER SET UNICODE NOT CASESPECIFIC,
  o_totalprice    DECIMAL(13,2) NOT NULL,
  o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  o_orderpriority CHARACTER(21)
                  CHARACTER SET UNICODE NOT CASESPECIFIC,
  o_comment       VARCHAR(79)
                  CHARACTER SET UNICODE NOT CASESPECIFIC)
PRIMARY INDEX OrdPI ( o_orderkey )
PARTITION BY (RANGE_N(o_orderpriority BETWEEN 'high'
                                      AND 'highest',
                                          'low'
                                      AND 'lowest',
                                          'medium'
                                      AND 'medium',
              NO RANGE OR UNKNOWN) )
UNIQUE INDEX (o_orderkey);

The following example shows the output of a request for this table against the DBC.IndexConstraints view using BTEQ. For the definition of this view, see Teradata Vantage™ - Data Dictionary, B035-1092.

SELECT *
FROM DBC.IndexConstraints
WHERE TableName = 'Orders'
AND   DatabaseName = 'DF2';

*** Query completed. One row found. 11 columns returned.
*** Total elapsed time was 1 second.

The DBC.IndexConstraints.CharSetID column contains the code of the same column name from DBC.CharTranslations representing the character set used as the collation when the corresponding table uses CHARSET_COLL collation.

The DBC.IndexConstraints.SessionMode column identifies the session mode, either ANSI or Teradata, in effect when the associated character partitioning was created or last modified.

The following report is an example of what you might see if you were to use BTEQ to run the previous SELECT example request against DBC.IndexConstraints.

DatabaseName          DF2
TableName             Orders
IndexName             OrdPI
IndexNumber           1
ConstraintType        Q
ConstraintText        CHECK ( (RANGE_N(o_orderpriority  BETWEEN 'high' AND 'hi
ConstraintCollation   A
CollationName         ASCII
CreatorName           DF2
CreateTimeStamp       2015-08-20 13:02:30
CharSetID             127
SessionMode           T

Partitioning Levels that Use Only a RANGE_N Function

The partitioning specifies character row partitioning levels that consist only of a RANGE_N function. You can only alter those levels to ADD or DROP the special NO RANGE and UNKNOWN partitions.

Row-partitioned Table Attributes You Can Change

Table Must Be Unpopulated

To change the character partitioning of a table using an ALTER TABLE … MODIFY PARTITION BY statement, the table must be unpopulated with rows.

Session Collation for the Modified Table

If the new partitioning also specifies character partitioning, the session collation in effect at the time the ALTER TABLE statement is submitted becomes the collation for the modified table.

Partitioning-related optimizations such as row partition elimination and deferred row partition delete operations involving the character row partitioning levels are restricted to SQL requests having the same session collation as was in effect when the table was created.

Vantage still uses row partition elimination on the non-character row partitioning levels of a character multilevel partitioning when collations do not match.

Partitioning CHECK Constraints

Vantage derives a partitioning CHECK constraint from the partitioning expressions. See Partitioning CHECK Constraints for Partitioned Tables With 2-Byte Partitioning and Partitioning CHECK Constraint for Partitioned Tables with 8-Byte Partitioning. The text for this index CHECK constraint must not exceed 16,000 characters, or roughly 2,000 partitions.

Character partitioning typically constrains the number of partitions you can define to a much smaller number than you can define for non-character partitioning.

The effective limit for character partitioning is roughly 2,000 partitions because of the 16,000 character limit on partitioning CHECK constraints. For very large tables, the most effective character partitioning in most cases is as part of a multilevel partitioning expression. Combining predicates on multiple partitioning levels might then reduce the ratio of combined partitions that need to be read from the rough limit of for a single-level character partitioning to up to the maximum number of partitions that can be defined for a table.

Using ADD RANGE or DROP RANGE for Multilevel Partitioning

The following rules apply:

If any of these errors occur, you must perform an ALTER TABLE … REVALIDATE request to revalidate the partitioning.