fbpx

Teradata Fast Path INSERT/SELECT

By DWH Pro Admin

December 8, 2019


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 is to minimize 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.

Teradata Fast Path
Data Blocks are assembled in parallel on each AMP and written to the target table

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.
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Next Article
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"rgb(255, 0, 0)"}},"gradients":[]}}]}__CONFIG_colors_palette__
GET OUR TERADATA BOOK

DWH Pro Admin

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>