title: Move billions of records from one table to another in teradata
body: <p>I have a teradata view, containing 1 billion records for each day and I need to process data for 1 year, so we have 365 billion records and Data is partitioned by date - interval each day.</p>
  
  <p>I need to Insert - select 3 ID columns(data will be grouped based on these) and 2 columns with measures (need to use SUM agg function)</p>
  
  <p>Query is something like below:</p>
  
  <pre><code>Insert into table1
  Select 
    col1, col2, col3, SUM(col4), SUM(col5)
  FROM 
    table2
  GROUP BY 
    col1, col2, col3
  WHERE coldate between 'date1' and 'date2';
  </code></pre>
  
  <p>The issue is that query keeps executing (didnt complete in 20 mins) if I run it for one day and I need to run this for 1 year.</p>
  
  <p>How should I approach - should I use MLOAD - insert select or what else?</p>
  
  <p>Please suggest, need resolution asap. Thanks</p>
  
  <pre><code>Explain SELECT 
      ORIGINATING_NUMBER_VAL,
      SUM(ACTIVITY_DURATION_MEAS),
      SUM(Upload_Data_Volume),
      SUM(Download_Data_Volume)
  FROM 
      dp_tab_view.NETWORK_ACTIVITY_DATA_RES
  WHERE
      CAST(Activity_Start_Dttm as DATE) between '2014-12-01' AND '2014-12-31'
  GROUP BY 
      ORIGINATING_NUMBER_VAL;
  
    1) First, we lock DP_TAB.NETWORK_ACTIVITY_DATA_RES in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access, we lock
       DP_TAB.NETWORK_ACTIVITY_DATA_BLC_2013 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access, we lock
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ4_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access, we lock
       DP_TAB.NETWORK_ACTIVITY_DATA_RES_BLC in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access, we lock
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ2_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access, we lock
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ1_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access, and we lock
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ3_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES for access.
    2) Next, we do an all-AMPs RETRIEVE step from 31 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_RES in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES with a condition of (
       "(DP_TAB.NETWORK_ACTIVITY_DATA_RES in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_RES in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '3015-02-09 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_RES in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2015-01-01 00:00:00'))") 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 size
       of Spool 1 is estimated with low confidence to be 1 row (70 bytes).
       The estimated time for this step is 37.22 seconds.
    3) We do an all-AMPs RETRIEVE step from 31 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_RES_BLC in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES with a condition of (
       "(DP_TAB.NETWORK_ACTIVITY_DATA_RES_BLC in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_RES_BLC in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2015-01-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_RES_BLC in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-10-13 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_RES_BLC in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '3015-02-10 00:00:00')))") 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 low confidence to be 22,856,337,679 rows
       (1,599,943,637,530 bytes).  The estimated time for this step is 1
       hour and 52 minutes.
    4) We do an all-AMPs RETRIEVE step from 0 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ1_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES by way of an all-rows scan
       with a condition of ("(DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ1_14 in
       view dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ1_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm&lt;
       TIMESTAMP '2015-01-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ1_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2014-04-01 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ1_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-01-01 00:00:00')))") 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 size of Spool 1 is estimated with low confidence to be
       22,856,337,680 rows (1,599,943,637,600 bytes).  The estimated time
       for this step is 0.01 seconds.
    5) We do an all-AMPs RETRIEVE step from 0 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ2_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES by way of an all-rows scan
       with a condition of ("(DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ2_14 in
       view dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ2_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm&lt;
       TIMESTAMP '2015-01-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ2_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2014-07-01 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ2_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-04-01 00:00:00')))") 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 size of Spool 1 is estimated with low confidence to be
       22,856,337,681 rows (1,599,943,637,670 bytes).  The estimated time
       for this step is 0.01 seconds.
    6) We do an all-AMPs RETRIEVE step from 0 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ3_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES by way of an all-rows scan
       with a condition of ("(DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ3_14 in
       view dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ3_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm&lt;
       TIMESTAMP '2014-10-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ3_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-07-01 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ3_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2015-01-01 00:00:00')))") 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 size of Spool 1 is estimated with low confidence to be
       22,856,337,682 rows (1,599,943,637,740 bytes).  The estimated time
       for this step is 0.01 seconds.
    7) We do an all-AMPs RETRIEVE step from 0 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ4_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES by way of an all-rows scan
       with a condition of ("(DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ4_14 in
       view dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ4_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm&lt;
       TIMESTAMP '2015-01-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ4_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2014-10-13 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_BLCQ4_14 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-10-01 00:00:00')))") 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 size of Spool 1 is estimated with low confidence to be
       22,856,337,683 rows (1,599,943,637,810 bytes).  The estimated time
       for this step is 0.01 seconds.
    8) We do an all-AMPs RETRIEVE step from 0 partitions of
       DP_TAB.NETWORK_ACTIVITY_DATA_BLC_2013 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES by way of an all-rows scan
       with a condition of ("(DP_TAB.NETWORK_ACTIVITY_DATA_BLC_2013 in
       view dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &gt;=
       TIMESTAMP '2014-12-01 00:00:00') AND
       ((DP_TAB.NETWORK_ACTIVITY_DATA_BLC_2013 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm&lt;
       TIMESTAMP '2014-01-01 00:00:00') AND
       (DP_TAB.NETWORK_ACTIVITY_DATA_BLC_2013 in view
       dp_tab_view.NETWORK_ACTIVITY_DATA_RES.Activity_Start_Dttm &lt;
       TIMESTAMP '2015-01-01 00:00:00'))") 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 size
       of Spool 1 is estimated with low confidence to be 22,856,337,684
       rows (1,599,943,637,880 bytes).  The estimated time for this step
       is 0.01 seconds.
    9) We do an all-AMPs SUM step to aggregate from Spool 1 (Last Use) by
       way of an all-rows scan with a condition of (
       "((CAST((NETWORK_ACTIVITY_DATA_RES.ACTIVITY_START_DTTM) AS
       DATE))&gt;= DATE '2014-12-01') AND
       ((CAST((NETWORK_ACTIVITY_DATA_RES.ACTIVITY_START_DTTM) AS DATE))&lt;=
       DATE '2014-12-31')") , grouping by field1 ( ORIGINATING_NUMBER_VAL).
       Aggregate Intermediate Results are computed globally, then placed
       in Spool 4.  The aggregate spool file will not be cached in memory.
       The size of Spool 4 is estimated with low confidence to be
       17,142,253,263 rows (1,628,514,059,985 bytes).  The estimated time
       for this step is 6 hours and 28 minutes.
   10) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
       an all-rows scan into Spool 2 (group_amps), which is built locally
       on the AMPs.  The result spool file will not be cached in memory.
       The size of Spool 2 is estimated with low confidence to be
       17,142,253,263 rows (1,165,673,221,884 bytes).  The estimated time
       for this step is 21 minutes and 27 seconds.
   11) Finally, we send out an END TRANSACTION step to all AMPs involved
       in processing the request.
    -&gt; The contents of Spool 2 are sent back to the user as the result of
       statement 1.  The total estimated time is 8 hours and 42 minutes.
  </code></pre>