Fast Path INSERT/SELECT is designed to load a single table as efficiently as possible.
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 minimizes access to the mass memory (Hard Disk, Solid State Disk).
This is done by creating whole data blocks on each AMP and then writing them to the mass storage with a single IO.
Under the conditions mentioned above (target table is empty, all tables involved have the same primary index), the following is ensured at the same time:
The rows of the target and source tables are already sorted on the same AMP and also in ascending order according to the same rowhash within the data blocks.
Therefore, each AMP can prepare the data blocks to be written locally and write them to the mass memory with a single IO.
How does BTEQ handle the Fast Path INSERT/SELECT?
BTEQ uses a special syntax that must be adhered to when several tables are to be loaded into the empty target table simultaneously using Fast Path:
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.