Archives of the TeradataForum

Message Posted: Fri, 08 Jul 2005 @ 06:47:20 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Charles J Dong:

          > Database DbName;
          > INSERT INTO Tbl1
          >   SELECT col1, col2, ... FROM TblA;
          > INSERT INTO Tbl1
          >   SELECT col1, col2, ... FROM TblB;
          > INSERT INTO ...

Instead of running several insert/select into a populated table you could try different way in BTEQ:

     create table newTbl1 as Tab1 with no data;

     INSERT INTO newTbl1
        SELECT col1, col2, ... FROM Tab1
     ;INSERT INTO newTbl1
        SELECT col1, col2, ... FROM TblA
     ;INSERT INTO newTbl1
        SELECT col1, col2, ... FROM TblB
     ;INSERT INTO ...
     ;

     drop table Tab1;

     rename newTab1 to Tab1;

The insert part is a multistatement (= a single transaction), because of the position of the semicolon: As long as a new command starts on the same line after the semicolon the statement is not finished.

If there's any problem the rollback will be immediate, because the target table was empty.

There's only one disadvantage: You'll need a huge spool space...

Check the Utilities manual for rcvmanager -> "cancel rollback on table", but it's another grandpa, i.e. command line utility.

No, if the optimizer knows that the transaction is finished after the ins/sel, it might omit the transient journal, if the target table was empty.

There are lots of minor differences, you usually use the sam session mode all the time.

Pre-V2R6 transient journal entries were written in blocks, but the rollback was done row-by-row. In V2R6 it's a block-rollback, too.

There's a reason why ins/upd/del using SQL is/was slow compared to OLTP databases: 20 years ago a Teradata system was loaded using a monthly load, later it was a weekly load, then a daily load using batch load utilities. There's was no need to do it with SQL.

Starting with "Active" warehousing/near real time load it was necessary to load with real SQL, that's why there's a huge effort to make that faster. With each release ins/upd/del is getting faster, sometimes it's 10 to 50 times faster.

Usually ins/sel into an empty table.

Dieter

	<Prev	Next>		<<First	<Prev	Next>	Last>>