Archives of the TeradataForum

Message Posted: Fri, 20 Mar 2009 @ 16:17:24 GMT

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

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.

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