See also Partitioning Expressions Using 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 follows.
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';
Result:
*** 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 name
- The component primary index or primary AMP index columns if the table has a primary index, and the partitioning expression, but only for an unpopulated table
- If the table is primary-indexed, you can change its UPI to a NUPI.
- If the table is primary-indexed, you can sometimes change its NUPI to a UPI.
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
- Use only RANGE_N partitioning.
- Do not make character data comparisons.
- Specify one or both of the following special partitions.
- NO RANGE
- UNKNOWN
The following rules apply:
IF the … | THEN … |
---|---|
session collation does not match the table collation | the request aborts and the system returns an error to the requestor. |
table collation is MULTINATIONAL and the installed MULTINATIONAL collation has changed since the table was created | |
table collation is CHARSET_COLL and the definition of the character set in DBC.Translation has changed since the table was created | |
session mode does not match the session mode that was in effect when the table was defined |
If any of these errors occur, you must perform an ALTER TABLE … REVALIDATE request to revalidate the partitioning.