Where do the rows go?

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

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.

An error occurs if there is no place to put a row unless you specify WITH DELETE or WITH INSERT clause which says what to do with such a row. 

This is also true if updating a partitioning column in a row -- the row is moved as needed (except in this case there is no WITH DELETE/INSERT clause so, if there is no associated partition with the new value, an error will occur for the update).  Depending on the new value set for the partitioning column this could entail moving the row to the NO RANGE, NO RANGE OR UNKNOWN, UNKNOWN, or other range partition.  If the test expression in the RANGE_N partitioning for a row was NULL and therefore the row was in the NO RANGE OR UNKNOWN or UNKNOWN partition (assuming one of these was defined for the RANGE_N partitioning), and the partitioning column value was changed so the test expression was not NULL, the row would be moved to a range partition or NO RANGE partition.

Note that it is the result of the test expression in the RANGE_N being NULL or not NULL, not the partitioning column being null or not null that determines that the partition should be UNKNOWN.  However, most often the test expression is just the partitioning column; in this case, if the partitioning column is defined as NOT NULL, there is no reason to define an UNKNOWN or NO RANGE OR UNKNOWN partition though you may need a NO RANGE partition.  In general, if the test expression can never be NULL or is never supposed to be NULL, an UNKNOWN or NO RANGE OR UNKNOWN partition should not be defined for the RANGE_N partitioning expression.

Finally:

  • If none of NO RANGE OR UNKNOWN, NO RANGE and UNKNOWN are specified, out-of-range and null partitioning values are rejected.
  • If NO RANGE is specified without UNKNOWN, null partitioning values for the test expression are rejected.
  • If UNKNOWN is specified without NO RANGE, out-of-range values are rejected.
6 Comments
N/A
Paul, thank you for this awesome article.. I'm not honestly competent in this question but I was reading with great interest.

convert m4a to mp3 | Ann
Enthusiast
Paul, Thank you for the article.

We stumbled across the following syntax that one of our developers began using and would like to understand the pros/cons.

How does the partition work since there is not a RANGE or CASE? It appears to be a Hash partition.
What happens when new values for db_source are entered?

The DDL for the table is:

CREATE SET TABLE cust_email ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
db_source SMALLINT NOT NULL,
loc_num SMALLINT NOT NULL,
cust_id INTEGER NOT NULL,
email_type SMALLINT NOT NULL,
email_address CHAR(100) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
row_id INTEGER,
insert_ts TIMESTAMP(3),
ero_send_email SMALLINT,
lst_upd_ts TIMESTAMP(3),
lst_upd_user CHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC,
lst_upd_cr_ts TIMESTAMP(3),
primary_contact SMALLINT,
dw_update_ts TIMESTAMP(0))
UNIQUE PRIMARY INDEX ( db_source ,loc_num ,cust_id ,email_type ,email_address )
PARTITION BY db_source ;

Thanks for any insight that you can provide,

Linda
Teradata Employee
PARTITION BY is followed by an expression which is used to compute the partition number in which to put a row on the AMP the row is sent to based on the hash of the primary index columns. RANGE_N and CASE_N are commonly used functions to use in this expression. However, the expression can simply be a column as in your example or some other expression. In that case, the value of the column (no hashing) in your example or the result of the expression is used as the partition number -- the value must be between 1 and 65535. Since db_source is a SMALLINT, it can only have values between -32768 and 32767, but since there are no partitions for null, 0 or negative numbers, if you try to insert a null, 0 or negative value for db_source, an error occurs.

The pro's of this approach is that it simple to specify, allows all positive values of db_source, and queries that reference db_source such that partition elimination can occur can benefit with improved performance.

Con's are that is not obvious how many partitions there could be (a RANGE_N can make the range of values allowed more obvious), possiblity that the partitioning is too fine grained if there is not very many rows per specific value of db_source (should be about 10 data blocks per nonempty partition per AMP -- empty partitions are not an issue as long as PARTITION statistics are collected), and this may allow illegal values to be inserted for db_source if the allowed values are actually fewer than 32767.
Enthusiast
I created one table with range_n partition like (partition by range_n col between date and date each interval '1' year, no_range)
After i performed alter to drop partition..that was succeeded, But i have a doubt
(I Didn't use WITH INSERT OPTION)
Are rows move to no_range?

Enthusiast

I created one table with range_n partition like (partition by range_n col between date and date each interval '1' year, no_range)
After i performed alter to drop partition..that was succeeded, But i have a doubt
(I Didn't use WITH INSERT OPTION)

Are Rows moved from the dropped partition to the save table?
Are Rows moved from the dropped partition to the NO RANGE partition?

Regards,-mack
Teradata Employee

To help make sure I answer your question correctly, here is a more complete example of what I think you asking about:

CREATE TABLE t1 (a INT, b INT, col DATE) PRIMARY INDEX (a) PARTITION BY RANGE_N(col BETWEEN DATE '2010-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH, NO RANGE);

ALTER TABLE t1 MODIFY PRIMARY INDEX DROP RANGE WHERE PARTITION BETWEEN 1 AND 12  /* drop the partitions for the first year (i.e., year 2010) */;

Are rows moved from the dropped partition to the save table?  No. Since you did not specify a WITH INSERT save_table option, you are telling Teradata you do not want to save any rows that would be deleted from the table due to dropping partitions. Also, in this example, since there is a NO RANGE partition, any rows for this table in the dropped partitions (in the year 2010) would not be deleted (so no rows would go into a save table even if you specified the WITH INSERT option) but rather any such rows would be moved to the NO RANGE partition.

Are rows moved from the dropped partition to the NO RANGE partition? Yes, since there is a NO RANGE partition, any rows in the dropped partitions for this example would be moved to the NO RANGE partition. This is true whether or not a WITH DELETE or WITH INSERT option is specified. Rows in a dropped partitioned are moved, rather than deleted (and placed in save table if one specified) if there are partitions to which they can be moved in the altered partitioning. If you had also added a single partition in the ALTER TABLE for all the rows of 2010, the rows would have been moved there instead of the NO RANGE partition.

If you submit the following query before and after the above ALTER TABLE, you should get the same result:

SELECT COUNT(*) FROM t1 WHERE col BETWEEN DATE '2010-01-01' AND DATE '2010-12-31';

However, the following query would get different results:

SELECT DISTINCT PARTITION FROM t1 WHERE col BETWEEN DATE '2010-01-01' AND DATE '2010-12-31';

Before the ALTER TABLE, it would return the values 1 through 12 (assuming each month in the year 2010 had at least one row). After the ALTER TABLE, it would return 181 for the NO RANGE partition (assuming there is at least one row for the year 2010). Note that prior to the ALTER TABLE, the NO RANGE partition would have been partition number 193.

If this doesn't answer your question or you have further questions, please let me know.