15.00 - Rules for Modifying a Character Partitioning Expression - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Rules for Modifying a Character Partitioning Expression

The following rules apply to adding and dropping ranges for character partitioning expressions. The are in addition to the rules specified for non‑character partitioning (see “Modifying the Partitioning of a Table Using the ADD RANGE and DROP RANGE Options” on page 96).

  • You can only modify character partitioning if the table is not populated with rows.
  • You can modify a non‑character partitioning level of a multilevel character partitioning if the partitioning expression has at least one partitioning level that does not specify a character range comparison.
  • To modify such a non‑character partitioning, you can specify ADD RANGE, DROP RANGE, or both as long as the session collation matches the table or join index collation and the transaction semantics match the transaction semantics that were in effect when the table or join index was created.

  • You can modify a character partitioning level of a character partitioning expression for the following cases.
  • The character partitioning level is defined using only a RANGE_N function.
  • The partitioning levels that you add or drop all use RANGE_N partitioning.
  • The partitioning levels do not involve character comparisons.
  • To ADD or DROP the special NO RANGE and UNKNOWN partitions.
  • The following additional rules must also be followed for this case.

  • The session collation must match the table or join index collation.
  • You can determine the collation for a table or join index by querying the system view DBC.IndexConstraints. Suppose you create this character‑partitioned table in database df2 with ASCII collation and Teradata session mode.

     
         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 ord_PI (o_orderkey)
         PARTITION BY (RANGE_N(o_orderpriority BETWEEN 'high' 
                                               AND     'highest', 
                       RANGE_N(o_orderpriority BETWEEN 'high' 
                                               AND 'highest',
                                               'low' 
                                               AND 'lowest',
                                               ‘medium' 
                                               AND 'medium',
                       NO RANGE OR UNKNOWN))
         UNIQUE INDEX (o_orderkey);
     

    The following report is the output of a query of the DBC.IndexConstraints view. You can see that the orders table was created in Teradata session mode (SessionMode=T) with ASCII collation (CollationName=ASCII).

     
         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 session mode must match the table or join index mode.
  • For example, if a character‑partitioned table was created in Teradata session mode, the ALTER TABLE request must also be executed in Teradata session mode.

  • If the table or join index collation is MULTINATIONAL, then the installed MULTINATIONAL collation cannot have changed since the object was created.
  • If the table or join index collation is CHARSET_COLL, the definition of the character set in DBC.Translation cannot have changed since the object was created.
  • If the version of Teradata Database has been backed down to a previous minor release and the character partitioning is defined with Unicode characters, the Unicode character set definition cannot have changed since the object was created.
  • If any of these rules are violated, Teradata Database aborts the request and returns an error to the requestor.