Empty Table INSERT . . . SELECT Requests and Performance - Teradata Database

Empty Table INSERT . . . SELECT Requests and Performance

An INSERT . . . SELECT optimizes performance when the target table is empty. If the target table has no data, INSERT . . . SELECT operates on an efficient block-by-block basis that bypasses journaling.

Normally, when inserting a row into a table, the system must make a corresponding entry into the TJ to roll back the inserted row in case the transaction aborts.

For inserts into populated tables, if the transaction aborts, the system deletes all inserts from the table one row at a time by scanning the TJ for the transaction-related RowIDs.

For inserts into empty tables, if the transaction aborts, the system can easily return the table to its original state by deleting all rows.

The advantages of using optimized INSERT . . . SELECTs are:

Block-at-a-time processing

Faster insert logic (that eliminates block merge complexity)

Instantaneous rollback for aborted INSERT . . . SELECTs

Example : INSERT ... SELECT

Using multiple Regional Sales History tables, build a single summary table by combining summaries from the different regions. Then insert these summaries into a single table via a multistatement INSERT . . . SELECT statement.

All multistatement INSERT . . . SELECT statements output to the same spool table. The output is sorted and inserted into an empty table.

Form a multistatement request by semicolon placement in BTEQ as shown below, or by placing statements in a single macro.

Note: If you execute each of the statements as separate requests, only the first statement is inserted into an empty table.

  INSERT into Summary_Table

  SELECT store, region,sum(sales),count(sale_item)

  FROM Region_1

  GROUP BY  1,2

  ;INSERT into Summary_Table

  SELECT region2, sum (sales), count(sale_item)

  FROM Region_2

  GROUP BY     1,2

      . . .

  ;INSERT into   Summary_Table

  SELECT region3, sum(sales), count(sale_item)

  FROM Region_N

  GROUP BY     1,2;