Teradata does allow two partitions to be created – the ‘UNKNOWN’ partition and the ‘NO RANGE/NO CASE’ partition. These need to explicitly defined by the user and are part of the 65536 ( 216-1) allowed partitions.
No Range/No Case: Define for all those values which are valid on their own but would not fit any defined range or case. Say the table is partitioned on employee joining date and the range is given as ‘2000-01-01’ (may be the first year of the company’s existence), and the operator is not sure about a joining date of a certain employee so she puts 1999-12-31. Since this is a valid date, it will go to the No Range partition if it has been defined as part of the table DDL. If not, the record will be rejected. Consider the following example.
DROP TABLE q2;
CREATE TABLE q2
( emp_id INTEGER,
emp_name VARCHAR(50),
dept_no BIGINT,
join_dt DATE)
PRIMARY INDEX (emp_id, join_dt)
PARTITION BY RANGE_N (join_dt BETWEEN '2000-01-01' AND '2013-12-31' EACH INTERVAL '1' YEAR);CREATE TABLE completed. 0 rows returned. Elapsed time = 00:00:00
INSERT INTO q2 VALUES(1004, 'Darren', 2, '1999-12-23');
INSERT Failed. [5728] Partitioning violation for table tduser.q2.
ALTER TABLE q2
MODIFY PRIMARY INDEX
ADD RANGE NO RANGE;ALTER TABLE completed. Elapsed time = 00:00:00
INSERT INTO q2 VALUES(1004, 'Darren', 2, '1999-12-23');
INSERT completed. 1 rows processed. Elapsed time = 00:00:00
Output directed to Answer window
Notice that the row is successfully inserted after the NO RANGE is added to the table partition using the ALTER TABLE statement.
Unknown: A similar necessity is for columns for which the value is not defined and that is possible only when it has a NULL value. A partitioning column defined as NOT NULL obviates the need for this partition.
INSERT INTO q2 VALUES(1005, 'Evan', 2, NULL);
INSERT Failed. [5728] Partitioning violation for table tduser.q2.
ALTER TABLE q2
MODIFY PRIMARY INDEX
ADD RANGE UNKNOWN;ALTER TABLE completed. Elapsed time = 00:00:00
INSERT INTO q2 VALUES(1005, 'Evan', 2, NULL);
INSERT completed. 1 rows processed. Elapsed time = 00:00:00
Output directed to Answer window
Let’s insert three more rows into this table which go into other partitions.
INSERT INTO q2 VALUES(1001, 'Alice', 2, '2002-12-23');
INSERT INTO q2 VALUES(1002, 'Bob', 2, '2001-12-23');
INSERT INTO q2 VALUES(1003, 'Cathy', 2, '2003-12-23');INSERT completed. 3 rows processed. Elapsed time = 00:00:00
Output directed to Answer window
Let’s check out which row has gone into which partition.
SELECT PARTITION, Q2.* FROM q2
ORDER BY emp_id;Dropping a partition: Note that a range partition can only be dropped from one side of the range. So the following command throws an error.
ALTER TABLE q2
MODIFY PRIMARY INDEX
DROP RANGE BETWEEN '2003-01-01' AND '2005-12-31' EACH INTERVAL '1' YEAR;ALTER TABLE Failed. [3732] The facility of altering the partitioning of a non-empty table by adding/dropping partitions other than partitions at the beginning or end of a RANGE_N partitioning expression at partitioning level one has not been implemented yet.
With (or without) delete: Teradata tries to do the following when a drop partition statement is issued:
1. Check if the partitioning allows the rows to be relocated to some other partition. No Range or No Case for example. Note there could be other possible destination partitions for Case_N.
2. If those rows can be shifted to any other partition(s) then the current partition is dropped and the rows are relocated.
3. If those rows cannot be relocated then check for the s WITH DELETE statement. If present then delete corresponding rows and drop partition. If not present, then fail the statement.
Note again: that the presence of WITH DELETE is material only when Teradata cannot find a partition for the rows to relocate to.
When Teradata can find a suitable partition, it will merely relocate the rows. Let’s consider the same example:
ALTER TABLE q2
MODIFY PRIMARY INDEX
DROP RANGE BETWEEN '2003-01-01' AND '2013-12-31' EACH INTERVAL '1' YEAR
WITH DELETE; -- The presence of the WITH DELETE is not material here since these rows are still defined under the 'NO RANGE' partitionALTER TABLE completed. Elapsed time = 00:00:00
Let’s check the number of partitions and the rows in them:
SELECT PARTITION, Q2.* FROM q2
ORDER BY emp_id;Note that the number of partitions has been shrunk to 5 from the previous 16, and Cathy shares the same ‘NO RANGE’ partition with Darren.
| partition | emp_id | emp_name | dept_no | join_dt |
| 3 | 1,001 | Alice | 2 | 12/23/2002 |
| 2 | 1,002 | Bob | 2 | 12/23/2001 |
| 4 | 1,003 | Cathy | 2 | 12/23/2003 |
| 15 | 1,004 | Darren | 2 | 12/23/1999 |
| 16 | 1,005 | Evan | 2 | ? |
Where With Delete becomes effective: If the UNKNOWN partition is dropped, the WITH DELETE has to specified, else the command will fail as described afore.
ALTER TABLE q2
MODIFY PRIMARY INDEX
DROP RANGE UNKNOWN;ALTER TABLE Failed. [5728] Partitioning violation for table tduser.q2.
Why? Because the row has no other partition to go to.
ALTER TABLE q2
MODIFY PRIMARY INDEX
DROP RANGE UNKNOWN
WITH DELETE;ALTER TABLE completed. Elapsed time = 00:00:00
SELECT PARTITION, Q2.* FROM q2
ORDER BY emp_id;Update on partitioning column: What if Alice’s joining date is suddenly overwritten by an application error?
UPDATE q2
SET join_dt = NULL
WHERE emp_id = 1001;UPDATE Failed. [5728] Partitioning violation for table tduser.q2.
Ha! the system won’t allow such a change as the row can’t be put into any known partition.
| partition | emp_id | emp_name | dept_no | join_dt |
| 3 | 1,001 | Alice | 2 | 12/23/2002 |
| 2 | 1,002 | Bob | 2 | 12/23/2001 |
| 4 | 1,003 | Cathy | 2 | 12/23/2003 |
| 4 | 1,004 | Darren | 2 | 12/23/1999 |
