Archives of the TeradataForum

Message Posted: Mon, 09 Sep 2002 @ 20:06:49 GMT


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

Subj:		Re: Macro to loop thru different databases

From:		Geoffrey Rommel

(2) Insert/select (note: this is *NOT* a multi-statement request):

It IS a multistatement request, because EVERY macro is a multistatement request, just look at EXPLAIN.

Well, I did look at the EXPLAIN, and it's different from what I thought was a multistatement request.

create macro lcidba.fatestm1
as
(insert into lcidba.fatest
  select * from lci_dw_tbls.lcixtfa_fty
   where fty_id_no < '0001000'
;insert into lcidba.fatest
  select * from lci_dw_tbls.lcixtfa_fty
   where fty_id_no between '0001000' and '0001999'
;insert into lcidba.fatest
  select * from lci_dw_tbls.lcixtfa_fty
   where fty_id_no between '0002000' and '0002999'; );

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

+---------+---------+---------+---------+---------+---------+---------+-
 /*==== The following is a multi-statement request, I believe. ====*/
explain
insert into lcidba.fatest
  select * from lci_dw_tbls.lcixtfa_fty
   where fty_id_no < '0001000'
;insert into lcidba.fatest
  select * from lci_dw_tbls.lcixtfa_fty
   where fty_id_no between '0001000' and '0001999'
;insert into lcidba.fatest
  select * from lci_dw_tbls.lcixtfa_fty
   where fty_id_no between '0002000' and '0002999';

 *** Help information returned. 34 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.lcixtfa_fty.`
	`2)`	`Next, we lock a distinct lcidba."pseudo table" for write on a RowHash to prevent global deadlock for lcidba.fatest.`
	`3)`	`We lock lci_dw_tbls.lcixtfa_fty for read, and we lock lcidba.fatest for write.`
	`4)`	`We do an all-AMPs RETRIEVE step from lci_dw_tbls.lcixtfa_fty by way of an all-rows scan with a condition of ("lci_dw_tbls.lcixtfa_fty.fty_id_no < '0001000'") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 5,728 rows. The estimated time for this step is 0.37 seconds.`
	`5)`	`We do an all-AMPs RETRIEVE step from lci_dw_tbls.lcixtfa_fty by way of an all-rows scan with a condition of ("(lci_dw_tbls.lcixtfa_fty.fty_id_no <= '0001999') AND (lci_dw_tbls.lcixtfa_fty.fty_id_no >= '0001000')") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 3,437 rows. The estimated time for this step is 0.37 seconds.`
	`6)`	`We do an all-AMPs RETRIEVE step from lci_dw_tbls.lcixtfa_fty by way of an all-rows scan with a condition of ("(lci_dw_tbls.lcixtfa_fty.fty_id_no <= '0002999') AND (lci_dw_tbls.lcixtfa_fty.fty_id_no >= '0002000')") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 3,437 rows. The estimated time for this step is 0.37 seconds.`
	`7)`	`We do a SORT to order Spool 1 by row hash.`
	`8)`	`We do a MERGE into lcidba.fatest from Spool 1 (Last Use).`
	`9)`	`We spoil the parser's dictionary cache for the table.`
	`10)`	`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. No rows are returned to the user as the result of statement 3.`

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

 /*==== The following is a macro. ====*/
explain
exec lcidba.fatestm1;

 *** Help information returned. 37 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.lcixtfa_fty.

2) Next, we lock a distinct lcidba."pseudo table" for write on a RowHash to prevent global deadlock for lcidba.fatest.

3) We lock lci_dw_tbls.lcixtfa_fty for read, and we lock lcidba.fatest for write.

4) We do an all-AMPs RETRIEVE step from lci_dw_tbls.lcixtfa_fty by way of an all-rows scan with a condition of ("lci_dw_tbls.lcixtfa_fty.fty_id_no < '0001000'") into Spool 1, which is built locally on the AMPs. The size of Spool 1 is estimated with no confidence to be 5,728 rows. The estimated time for this step is 0.25 seconds.

5) We execute the following steps in parallel.

1) We do a MERGE into lcidba.fatest from Spool 1 (Last Use).

2) We do an all-AMPs RETRIEVE step from lci_dw_tbls.lcixtfa_fty by way of an all-rows scan with a condition of ("(lci_dw_tbls.lcixtfa_fty.fty_id_no <= '0001999') AND (lci_dw_tbls.lcixtfa_fty.fty_id_no >= '0001000')") into Spool 2, which is built locally on the AMPs. The size of Spool 2 is estimated with no confidence to be 3,437 rows. The estimated time for this step is 0.24 seconds.

6) We execute the following steps in parallel. - 1) We do a MERGE into lcidba.fatest from Spool 2 (Last Use). - 2) We do an all-AMPs RETRIEVE step from lci_dw_tbls.lcixtfa_fty by way of an all-rows scan with a condition of ("(lci_dw_tbls.lcixtfa_fty.fty_id_no <= '0002999') AND (lci_dw_tbls.lcixtfa_fty.fty_id_no >= '0002000')") into Spool 3, which is built locally on the AMPs. The size of Spool 3 is estimated with no confidence to be 3,437 rows. The estimated time for this step is 0.24 seconds. 7) We do a MERGE into lcidba.fatest from Spool 3 (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. No rows are returned to the user as the result of statement 3.


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

Attachments

Library

Quick Reference