![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Wed, 11 Sep 2002 @ 12:42:42 GMT
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? :-)
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.
| |