Efficiently Load Tables with Teradata Fast Path INSERT/SELECT: Requirements and Advantages

Fast Path INSERT/SELECT efficiently loads a single table.

Requirements for Teradata Fast Path INSERT /SELECT

• All tables involved have the same primary index.
• The target table is empty.
• Only INSERT statements can be included in the request.

How does the Fast Path INSERT/SELECT work?

This reduces access to mass storage (such as hard disk or solid-state disk).

The process involves generating complete data blocks on every AMP and subsequently transferring them to mass storage using a single IO.

Under these conditions (with an empty target table and all relevant tables sharing identical primary indexes), the following is simultaneously guaranteed:

The rows of the target and source tables are sorted in ascending order on the same AMP and according to the same rowhash within the data blocks.

Each AMP can locally prepare data blocks and write them to mass storage using a single IO.

How does BTEQ handle the Fast Path INSERT/SELECT?

When using Fast Path to load multiple tables into an empty target table, you must strictly follow the specific syntax required by BTEQ.

The semicolon must be at the beginning (from the 2nd table) of the line (not at the end of the lines!):

INSERT INTO Customer SELECT * FROM Customer1
;INSERT INTO Customer SELECT * FROM Customer2
;INSERT INTO Customer SELECT * FROM Customer3
;INSERT INTO Customer SELECT * FROM Customer4

Advantages of the Fast Path INSERT/SELECT

• Only one row is stored in the transient journal for a possible ROLLBACK
• ROLLBACK is done immediately without delay
• Rows are packed into data blocks, and the whole blocks are written with one IO
• Since the tables all have the same primary index, the processing is AMP-local, and BYNET is not loaded.