Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 11 Sep 2002 @ 12:42:42 GMT


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


Subj:   Transactions, requests, statements, macros
 
From:   Geoffrey Rommel

I'm starting a new thread because we've kinda veered off the original subject.

Verdammt! Dieter is right. Don't you hate it when that happens? :-)


  Try the following:  


   ins tab1 sel * from tabx sample 1
   ;ins tab1 sel * from tabx;
  This will be a fast-path insert  


   create macro test as
   (ins tab1 sel * from tabx sample 1;
   ins tab1 sel * from taby;
   );

   exec test;
  Look at execution time and transient journal sizes.  



As output #1 below shows, Dieter is right: the macro does a fast-path insert/select. I think, however, that this must be a special optimization for this one case. In general, it would not be possible to execute every statement in a macro "in parallel". In output #2, I show an example: a select followed by an insert. Here the select must happen either before or after the insert. Fortunately, it happens just where you would expct it.

===== OUTPUT #1 =====

create table tab1
as
lci_dw_tbls.npmatvi_hvac_sl_prd
with no data;

 *** Table has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

--insert a single row into an empty table
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838;

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-
--insert lots of rows (will be journalled)
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;

 *** Insert completed. 900644 rows added.
 *** Total elapsed time was 3 seconds.

+---------+---------+---------+---------+---------+---------+---------+-

delete from tab1 all;

 *** Delete completed. 900645 rows removed.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-
--This will be a fast-path insert
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838
;ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;

 *** Insert completed. No rows added.
 *** Total elapsed time was 1 second.

 *** Insert completed. 900645 rows added.

+---------+---------+---------+---------+---------+---------+---------+-

-- NOW the macro:

create macro dieter_test as
(ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838
;ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;
);

 *** Macro has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

delete from tab1 all;

 *** Delete completed. 900645 rows removed.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

exec dieter_test;

 *** Insert completed. No rows added.
 *** Total elapsed time was 1 second.

 *** Insert completed. 900645 rows added.

+---------+---------+---------+---------+---------+---------+---------+-

-- EXPLAIN:
explain
ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no = 644838
;ins tab1 sel * from lci_dw_tbls.npmatvi_hvac_sl_prd
  where hvac_sl_prd_id_no <> 644838;

 *** Help information returned. 26 rows.
 *** Total elapsed time was 1 second.

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct lci_dw_tbls."pseudo table" for read on a RowHash to prevent global deadlock for lci_dw_tbls.npmatvi_hvac_sl_prd.  
  2)Next, we lock a distinct LCIDBA."pseudo table" for write on a RowHash to prevent global deadlock for LCIDBA.tab1.  
  3)We lock lci_dw_tbls.npmatvi_hvac_sl_prd for read, and we lock LCIDBA.tab1 for write.  
  4)We do a single-AMP RETRIEVE step from lci_dw_tbls.npmatvi_hvac_sl_prd by way of the unique primary index "lci_dw_tbls.npmatvi_hvac_sl_prd.hvac_sl_prd_id_no = 644838" with no residual conditions into Spool 1, which is built locally on that AMP. The size of Spool 1 is estimated with high confidence to be 1 row. The estimated time for this step is 0.15 seconds.  
  5)We do an all-AMPs RETRIEVE step from lci_dw_tbls.npmatvi_hvac_sl_prd by way of an all-rows scan with a condition of ("lci_dw_tbls.npmatvi_hvac_sl_prd.hvac_sl_prd_id_no <> 644838") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 900,644 rows. The estimated time for this step is 1.42 seconds.  
  6)We do a SORT to order Spool 1 by row hash.  
  7)We do a MERGE into LCIDBA.tab1 from Spool 1 (Last Use).  
  8)We spoil the parser's dictionary cache for the table.  
  9)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->No rows are returned to the user as the result of statement 1. No rows are returned to the user as the result of statement 2.  

+---------+---------+---------+---------+---------+---------+---------+-

explain
exec dieter_test;

 *** Help information returned. 26 rows.
 *** Total elapsed time was 1 second.

[Yeah, it's the same. Excerpt:]

       
  5)We do an all-AMPs RETRIEVE step from lci_dw_tbls.npmatvi_hvac_sl_prd by way of an all-rows scan with a condition of ("lci_dw_tbls.npmatvi_hvac_sl_prd.hvac_sl_prd_id_no <> 644838") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 900,644 rows. The estimated time for this step is 1.42 seconds.  
  6)We do a SORT to order Spool 1 by row hash.  
  7)We do a MERGE into LCIDBA.tab1 from Spool 1 (Last Use).  


===== OUTPUT #2 =====

create table tab2
( numerus    integer,
  nomen      char(10) )
unique primary index(numerus);

 *** Table has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-

ins tab2 (1, 'one');
ins tab2 (2, 'two');
ins tab2 (3, 'three');
ins tab2 (4, 'four');
ins tab2 (5, 'five');

+---------+---------+---------+---------+---------+---------+---------+-

create macro dieter_test2 as
(select * from tab2
;ins tab2 (6, 'six')
;);

 *** Macro has been created.
 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+-----

First explain, then run.

explain
exec dieter_test2;

 *** Help information returned. 14 rows.
 *** Total elapsed time was 1 second.
Explanation
--------------------------------------------------
 
  1)First, we lock a distinct LCIDBA."pseudo table" for read on a RowHash to prevent global deadlock for LCIDBA.tab2.  
  2)Next, we lock LCIDBA.tab2 for read.  
  3)We do an all-AMPs RETRIEVE step from LCIDBA.tab2 by way of an all-rows scan with no residual conditions into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 320 rows. The estimated time for this step is 0.15 seconds.  
  4)We do an INSERT into LCIDBA.tab2.  
  5)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1. No rows are returned to the user as the result of statement 2.  


+---------+---------+---------+---------+---------+---------+---------+----

exec dieter_test2;

 *** Query completed. 5 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

    numerus  nomen
-----------  ----------
          2  two
          4  four
          1  one
          3  three
          5  five

 *** Insert completed. One row added.


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