Range constraints are data description phrases that are entered into the Teradata SQL CREATE TABLE statement that limit the range of acceptable values for a column. Since the range constraint checks occur while Teradata FastLoad inserts data into the Teradata FastLoad table, the number of range constraints in the Teradata FastLoad job script has a direct impact on the performance of Teradata FastLoad.
Range Constraint Types
The following table list the two types of range constraints, explicit and implicit.
| Constraint Type | Example |
|---|---|
| Explicit | The Salary column range of between 1 and 99000.00, as shown in the following CREATE TABLE example. |
| Implicit | The DeptNo column range of ZZ9, as shown in the following CREATE TABLE example. |
Range Constraint Examples
The following Teradata SQL CREATE TABLE statement shows the two types of range constraint phrases for the Salary and DeptNo columns:
CREATE TABLE Employee
(EmpNo INTEGER FORMAT ‘ZZZZ9’,
Name VARCHAR (12) CASESPECIFIC TITLE
‘Employee//Name’, DeptNo INTEGER FORMAT ‘zz9’
TITLE ‘Dept#’,
Salary DECIMAL (7,2) BETWEEN 1 AND 99000.00
FORMAT ‘ZZ,ZZ9.99’)
UNIQUE PRIMARY INDEX (EmpNo);
- Salary is in the range of 1 to 99000.00
- DeptNo is between -999 and 999
If it is known that the values for the DeptNo column are always in the range of -999 to 999, then they can improve the performance of the Teradata FastLoad job by removing the ZZ9 phrase from the CREATE TABLE statement in the Teradata FastLoad job script.