Archives of the TeradataForum

Message Posted: Tue, 16 May 2006 @ 17:32:29 GMT

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

I have a question about transient journaling.

Given: Teradata V2R6. TblA is a large table that is already populated. Tbl B is smaller and populated as well. TBLA and TBLB have the same PI, no secondary indexes, no compressed columns, the same stats definition and stats are collected on both tables. There is no PJ on either table.

Both tables are not fallback protected.

Given these two SQL:

SQL_1

     Delete from TBLA
     ;Insert into TBLA
      select cola, sum(colb) from TBLB group by 1;

SQL_2

     Delete from TBLA ;

     Insert into TBLA select cola, sum(colb) from TBLB group by 1 ;

I believe SQL_1 will create TJ rows for every row in the delete and every row in the insert because the tables are never empty in a comitted state because of the multistatement request.

I believe SQL_2 will create only two entries in the TJ. One for the delete and one for the insert and "fast path" block inserts would be enabled for SQL_2.

In the multi-statement request SQL_1, will transient journaling take place in all rows for both the delete and the insert? Is V2R6 smart enough to see that SQL_1 and SQL_2 are functionally the same and "fast path" can be used in SQL_1?

Are "fast path" deletes and inserts disabled if the tables are fallback protected, making this whole issue moot.

Please either verify or correct my understanding.

Thanks,

Randall Barbour
USPS

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