Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 16 May 2006 @ 19:10:15 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Transient Journaling question
From:   Dieter Noeth

Randall Barbour wrote:

  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:  


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

          > 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.  

Ack, if it's run in a Teradata session.

In ANSI session it's probably:

     Delete from TBLA ; commit;

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

  In the multi-statement request SQL_1, will transient journaling take place in all rows for both the delete and the insert?  

Yep, simply try it and compare run time/TJ size :-)

  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?  

No they're not the same: 1 transaction vs. 2 transactions

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

No, only FK/Triggers/Join Indexes/PJ?/?? switch disable fastpath.

Teradata version/Fallback/table size/PI/SIs/compress/stats don't matter with regard to TJ.

  Please either verify or correct my understanding.  

Correct, at least it's what i usually tell my students :-)


  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020