15.00 - Range Constraints - FastLoad

Teradata FastLoad Reference

prodname
FastLoad
vrm_release
15.00
category
Programming Reference
featnum
B035-2411-034K

Range Constraints

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

Table 12 list the two types of range constraints, explicit and implicit.

 

Table 12: Range Constraint Types 

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);

Before inserting each row in the Employee table, Teradata FastLoad checks to verify that the value for:

  • 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.