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 the mass storage using a singular IO.
In the aforementioned circumstances (with an empty target table and all relevant tables possessing identical primary indexes), simultaneous assurance of the following is 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 memory using a single IO.
How does BTEQ handle the Fast Path INSERT/SELECT?
When utilizing Fast Path to load multiple tables into an empty target table, it is necessary to adhere to the specific syntax utilized by BTEQ strictly.
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.
