title: How to improve the SQL insert query performance?
body: <p>I am getting very long response time while querying data (83 million rows approx.) from one table and inserting it into another table in the same schema. It takes almost one and half hour. The query looks like below:</p>
  
  <pre><code>INSERT
  INTO TABLE_A
    (
      DIM_KEY,
      CLIENT_KEY,
      CONSUMER_DIM_KEY,
      PRCS_WK
    )
    (
      ((
      (SELECT DIM_KEY,
        client_KEY,
        consumer_dim_key,
        prcs_WK
      FROM
            (*SELECT DISTINCT DIM_KEY,
          client_KEY,
          prcs_WK,
          consumer_dim_key,
          SUM(SALE_AMOUNT) OVER(PARTITION BY CONSUMER_DIM_KEY) AS SALE_AMOUNT
            FROM FACT_TABLE
            WHERE DIM_KEY  = '300067'
            AND CRITERIA_ID   = '1234'
            AND IS_EXISTS = 'N'*
        )
      WHERE SALE_AMOUNT &gt; 0
      )
    UNION
      (SELECT DIM_KEY,
        client_KEY,
        consumer_dim_key,
        prcs_WK
      FROM
            (*SELECT DISTINCT DIM_KEY,
          client_KEY,
          prcs_WK,
          consumer_dim_key,
          SUM(SALE_AMOUNT) OVER(PARTITION BY CONSUMER_DIM_KEY) AS SALE_AMOUNT
            FROM FACT_TABLE
            WHERE DIM_KEY  = '300067'
            AND CRITERIA_ID   = '1235'
            AND IS_EXISTS = 'N'*
        )
      WHERE SALE_AMOUNT &gt; 0
      ))
    UNION
      (SELECT DIM_KEY,
        client_KEY,
        consumer_dim_key,
        prcs_WK
      FROM
            (*SELECT DISTINCT DIM_KEY,
          client_KEY,
          prcs_WK,
          consumer_dim_key,
          SUM(SALE_AMOUNT) OVER(PARTITION BY CONSUMER_DIM_KEY) AS SALE_AMOUNT
            FROM FACT_TABLE
            WHERE DIM_KEY  = '300067'
            AND CRITERIA_ID   = '1236'
            AND IS_EXISTS = 'N'*
        )
      WHERE SALE_AMOUNT &gt; 0
      ))
    )
  </code></pre>
  
  <p>Here all the tables are present in the same schema.
   The queries marked * returns number of rows as:</p>
  
  <pre>
  Sub-query 1: 80 million rows
  Sub-query 2: 3.1 million rows 
  Sub-query 3: 0.2 million row
  </pre>