Subscribe to Blog content and comments for PaulSinclair Latest blog posts

In my blog entries "Teradata Columnar" and "9.2 Quintillion? What's that all about?", I discussed column partitioning and the increased partition limit that are introduced in TD 14.0.  But there are other TD 14.0 partitioning enhancements that you may find useful.  The following provides a brief introduction to these enhancements; for more detailed information, see the Orange Book: Increased Partition Limit and other Partitioning Enhancements, the Orange Book: Teradata Columnar, and the TD 14.0 manuals.

A quintillion is 10 raised to the power of 18 (that is, 1 followed by 18 zeros).  9.2 quintillion (or more precisely, 9,223,372,036,854,775,807) is the new limit in Teradata Database 14.0 for the number of combined partitions.  Note that this is the same as the maximum BIGINT value (which is a signed 8-byte integer).  While tables that are defined with 65535 or less combined partitions still use 2-byte partitioning, if the number of defined combined partitions exceeds 65535 for a table, the table is created with 8-byte partitioning.

With row partitioning (for a PPI or column-partitioned table), the Teradata Database makes sure rows are placed in their appropriate partitions.  When the row partitioning for the table is altered, rows may need to move from one partition to another so they are in their appropriate partitions according to the altered partitioning.  The partitioning expression must be deterministic (always computes the same value for the same value of the partitioning column) to properly place and retrieve the row.

For instance if the table has RANGE_N partitioning, ALTER TABLE will move rows automatically from NO RANGE to newly-added ranges as needed.  Also, if a range is dropped, rows from that partition are moved to the NO RANGE, NO RANGE OR UNKNOWN partition or a newly-added range that covers the dropped range.

Let's start by understanding what a primary index does.  A primary index (PI) is used to distribute the rows of a table to the AMPs and on each AMP to order the rows by hash value within the combined partitions defined by a PARTITION BY clause (if this clause is not specified, there is just one partition, that is, the entire table).

Teradata 14.0 introduces Teradata Columnar – a new option to organize the data of a user-defined table or join index on disk.

Teradata Columnar offers the ability to partition a table or join index by column.  It introduces column-storage as an alternative choice to row-storage for a column partition and autocompression.  Column partitioning can be used alone in a single-level partitioning definition or with row partitioning in a multilevel partitioning definition.

I was recently asked about NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN partitions for the RANGE_N function.  While these have been available since TD V2R5.0, there is sometimes confusion about what they mean and when to use or not use them.