Archives of the TeradataForum
Message Posted: Tue, 16 May 2006 @ 19:10:15 GMT
| 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. | |
> 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. | |
Ack
| | 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 :-)
Dieter
| 
