|
|
Archives of the TeradataForum
Message Posted: Fri, 20 Mar 2009 @ 16:17:24 GMT
Subj: | | Re: Why redistribution in this select with UNION |
|
From: | | Simard Rudel |
When I look at the explain plan,I prefer this solution than UNION ALL.
Thank for all the answer, it was very usefull for me and interesting for the
Teradata community.
explain
insert into espa_trav_57.Table_Dummy
select * from espa_trav_57.Table_Dummy_1
union all
select * from espa_trav_57.Table_Dummy_2;
Explanation
1) First, we lock a distinct espa_trav_57."pseudo table" for read on
a RowHash to prevent global deadlock for
espa_trav_57.Table_Dummy_2.
2) Next, we lock a distinct espa_trav_57."pseudo table" for read on a
RowHash to prevent global deadlock for espa_trav_57.Table_Dummy_1.
3) We lock a distinct espa_trav_57."pseudo table" for write on a
RowHash to prevent global deadlock for espa_trav_57.Table_Dummy.
4) We lock espa_trav_57.Table_Dummy_2 for read, we lock
espa_trav_57.Table_Dummy_1 for read, and we lock
espa_trav_57.Table_Dummy for write.
5) We do an all-AMPs RETRIEVE step from espa_trav_57.Table_Dummy_1 by
way of an all-rows scan with no residual conditions into Spool 1
(all_amps), which is built locally on the AMPs. The input table
will not be cached in memory, but it is eligible for synchronized
scanning. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with high confidence to be
500,000,000 rows. The estimated time for this step is 53.37
seconds.
6) We do an all-AMPs RETRIEVE step from espa_trav_57.Table_Dummy_2 by
way of an all-rows scan with no residual conditions into Spool 1
(all_amps), which is built locally on the AMPs. The input table
will not be cached in memory, but it is eligible for synchronized
scanning. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with high confidence to be
700,000,000 rows. The estimated time for this step is 21.35
seconds.
7) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan into Spool 2 (all_amps), which is built locally
on the AMPs. Then we do a SORT to order Spool 2 by row hash. The
result spool file will not be cached in memory. The size of Spool
2 is estimated with high confidence to be 700,000,000 rows. The
estimated time for this step is 11 minutes and 45 seconds.
8) We do an all-AMPs MERGE into espa_trav_57.Table_Dummy from Spool 2
(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.
insert into espa_trav_57.Table_Dummy
select * from espa_trav_57.Table_Dummy_1
;insert into espa_trav_57.Table_Dummy
select * from espa_trav_57.Table_Dummy_2
;
Explanation
1) First, we lock a distinct espa_trav_57."pseudo table" for read on
a RowHash to prevent global deadlock for
espa_trav_57.Table_Dummy_2.
2) Next, we lock a distinct espa_trav_57."pseudo table" for read on a
RowHash to prevent global deadlock for espa_trav_57.Table_Dummy_1.
3) We lock a distinct espa_trav_57."pseudo table" for write on a
RowHash to prevent global deadlock for espa_trav_57.Table_Dummy.
4) We lock espa_trav_57.Table_Dummy_2 for read, we lock
espa_trav_57.Table_Dummy_1 for read, and we lock
espa_trav_57.Table_Dummy for write.
5) We do an all-AMPs RETRIEVE step from espa_trav_57.Table_Dummy_1 by
way of an all-rows scan with no residual conditions into Spool 1
(all_amps), which is built locally on the AMPs. The input table
will not be cached in memory, but it is eligible for synchronized
scanning. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with high confidence to be
500,000,000 rows. The estimated time for this step is 53.37
seconds.
6) We do an all-AMPs RETRIEVE step from espa_trav_57.Table_Dummy_2 by
way of an all-rows scan with no residual conditions into Spool 1
(all_amps), which is built locally on the AMPs. The input table
will not be cached in memory, but it is eligible for synchronized
scanning. The result spool file will not be cached in memory.
The size of Spool 1 is estimated with high confidence to be
200,000,000 rows. The estimated time for this step is 21.35
seconds.
7) We do a SORT to order Spool 1 by row hash.
8) We do an all-AMPs MERGE into espa_trav_57.Table_Dummy 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.
| |