inserting

[kavira]

Hi,
I came to know that Updating or inserting into populated tables in Teradata can be very slow ... , COuld anyone pls explain me the reason.

Instead of updating or Inserting into a populated table, is it better to insert into an empty table 1st and then merge your newly inserted table with the populated table.==>how i can merge two tables and what will be the advantages?

Thanks!
kavira

 

[BillDHS]

Inserting single rows into any DB can be slow. It is a function of the work that must be performed: checking integrity, checking duplicates, adding rows to secondary indexes.

With loding empty tables the rows are added in blocks rather than individual rows, and if secondary indexes are drop at the beginning and recreated at the end, speed is increased.

 

[TheTeradataKid]

It is faster because of the Fastload utility loading the data in blocks. The updates and inserts does the operations row at a time resulting on full htable scans if there are any conditional variables to meet.

The Multiload utility is very fast also and can hande conditions and redefinitions in the data layouts.

 

[SiMCard]

A way to do it:

/* Populate NEW table (empty copy of OLD_TABLE) */
INSERT INTO NEW_TABLE
SELECT <new_data>
;

/* Use MSR to merge the OLD and NEW Tables */
INSERT INTO MIRROR_TABLE
SELECT * FROM NEW_TABLE
;INSERT INTO MIRROR_TABLE
SELECT * FROM OLD_TABLE
;

Then either:

DROP TABLE OLD_TABLE;

RENAME TABLE MIRROR_TABLE AS OLD_TABLE;

or:

DELETE FROM OLD_TABLE ALL;

INSERT INTO OLD_TABLE
SELECT * FROM MIRROR_TABLE;

Hope this helps with the 2nd part of the query.

SiMCard

 

[rohitrevo]

Updating or inserting into an populated table is slower because the if the target table has data, INSERT SELECT operates on a row-by-row basis. The INSERT SELECT operation operates on a block-by-block basis. The performance is optimised when the target table is empty, and has the same PI as the source table(s).

Teradata has a Transient journal in which a copy of the old row to be modified is placed. A 'write' of the inserted row and of the Transient Journal entry takes place on a row-by-row basis. Should a transaction abort for any reason, all inserts are deleted from the table one row at a time by scanning the TJ for RowIDs.

This is redundant in an empty table and due to Block-at-a-time processing the source rows (rows to be selected) and target rows (rows to be inserted) are on the same AMP as the tables should have same Primary Index. Similar primary index values hash similarly and are stored in the same AMP. Thus there is only One Transient Journal entry per AMP. This makes it optimised and its very fast.