15.00 - Rules for Altering the Row Partitioning for Character Partitioning - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules for Altering the Row Partitioning for Character Partitioning

The following rules and restrictions apply in addition to all other rules for using ALTER TABLE requests to modify row‑partitioned tables. The same rules for character partitioning apply to column‑partitioned tables and join indexes that mix row partitioning and column partitioning.

  • When you specify PARTITION BY for a table or join index and the partitioning expression compares CHARACTER or CHARACTER SET GRAPHIC data, the partitioning is called character partitioning.
  • The partitioning columns, those columns referenced in the partitioning expressions, might or might not have a character data type, but the resulting data type of at least one expression involving any of those columns has a character data type whether it is a simple CAST or a more complex expression.

  • Character partitioning is valid anywhere that non‑character partitioning is allowed, including as part of multilevel partitioning. If any of the partitioning levels in a multilevel partitioning definition is a RANGE_N or CASE_N function involving character comparison, then the entire partitioning expression is considered to specify multilevel character partitioning.
  • A new character partitioning expression can specify CHARACTER or CHARACTER SET GRAPHIC comparisons, but cannot specify Kanji1 columns, KanjiSJIS columns, or constant expressions.
  • The test value of a RANGE_N expressions cannot specify LONG VARCHAR or LONG VARCHAR CHARACTER SET GRAPHIC data types.
  • Expressions and referenced columns in the partitioning expression cannot have BLOB or CLOB data types.
  • Expressions and referenced columns in the partitioning expression cannot contain any of the following.
  • UDFs
  • UDTs
  • RANDOM function
  • Aggregate functions
  • Grouped‑row OLAP functions
  • Built‑in functions
  • System‑derived ROWID columns
  • Set operators
  • Subqueries
  • You can use ALTER TABLE requests to modify character partitioning only for the following conditions.
  • The table or join index is not populated with rows and the partitioning is changed by specifying a new PARTITION BY expression.
  • 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 (see Data Dictionary for the definition of this view) using BTEQ.

     
         .SIDETITLES
         .FOLDLINE
     
         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.

     
  • 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.
  • As is true for non‑character‑based partitioning, you can change the following attributes of a row‑partitioned table or join index using ALTER TABLE requests.
  • Its name
  • The component primary index columns if the table or join index has a primary index, and the partitioning expression, but only for an unpopulated table
  • If the table is primary‑indexed, you can changes its UPI to a NUPI.
  • If the table is primary‑indexed, you can sometimes change its NUPI to a UPI.
  • If you attempt to change the character partitioning of a table using an ALTER TABLE … MODIFY [PRIMARY INDEX] PARTITION BY request, then the table must be unpopulated with rows.
  • Otherwise, Teradata Database aborts the request and returns an error to the requestor.

  • If the new partitioning also specifies character partitioning, the session collation in effect at the time the ALTER TABLE request is submitted becomes the collation for the modified table or join index.
  • Single‑level 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.
  • Teradata Database can use combine column and row partition elimination for column‑partitioned tables and join indexes to optimize queries.

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

  • As it does for non‑character partitioning, Teradata Database derives a partitioning CHECK constraint from the partitioning expressions (see “Partitioning CHECK Constraints for Partitioned Tables With 2‑Byte Partitioning” on page 630 and “Partitioning CHECK Constraint for Partitioned Tables With 8‑Byte Partitioning” on page 632). The text for this index CHECK constraint must not exceed 16,000 characters, or roughly 2,000 partitions. Otherwise, Teradata Database aborts the request and returns an error to the requestor.
  • Character partitioning typically constrains the number of partitions you can define to a much smaller number than you can define for non‑character partitioning.

    This is because the effective limit for character partitioning is roughly 2,000 partitions because of the 16,000 character limit on partitioning CHECK constraints. As a result, for very large tables, the most effective means of using 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.

  • You can alter character partitioning using the ADD RANGE or DROP RANGE options for multilevel partitioning if the partitioning levels being added or dropped obey one or all of the following rules.
  • They use only RANGE_N partitioning.
  • They do not make character data comparisons.
  • They specify one or both of the following special partitions.
  • NO RANGE
  • UNKNOWN
  • Otherwise, the request aborts and the system returns an error to the requestor.

    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 [PRIMARY INDEX] request to revalidate the partitioning.

  • You can alter character partitioning using the ADD RANGE or DROP RANGE options if the partitioning is multilevel and the partitioning levels being added or dropped obey one or all of the following rules.
  • They use only RANGE_N partitioning.
  • They do not make character data comparisons.
  • They specify one or both of the following special partitions.
  • NO RANGE
  • UNKNOWN
  • Otherwise, the request aborts and the system returns an error to the requestor.