Range Constraints - FastLoad

Teradata FastLoad Reference

Product
FastLoad
Release Number
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-05-18
dita:mapPath
tjz1488824663315.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2411
lifecycle
previous
Product Category
Teradata Tools and Utilities

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.

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.