CODEWITHSUNDEEP
sqloracle-database
Amit Singh
Amit Singh

Reputation: 39

How to improve the SQL insert query performance?

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:

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 > 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 > 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 > 0
    ))
  )

Here all the tables are present in the same schema. The queries marked * returns number of rows as:

Sub-query 1: 80 million rows
Sub-query 2: 3.1 million rows 
Sub-query 3: 0.2 million row

Upvotes: 2

Views: 17487

Answers (1)

neshkeev
neshkeev

Reputation: 6486

  • You can try to use the APPEND hint to make oracle write new data over the HWM mark.
  • If you have indexes oracle wastes some times to have them up-do-date. You can also disable them and rebuild after the insert finishes.
  • Also if there are 83 millions rows then the insert generate a lot of the REDO information. To disable redo log generation of a table use the nologging option: alter table YOUR_TABLE nologging;
  • Also you can use asynchronous writing into your online redo log with commit_wait, commit_logging tips
  • You can set up a job queue to schedule a long-running operation in the background. To read more use this
  • You can use parallel DML

This is all the suggestions I can give you without having the explain plan.

Upvotes: 5

Related Questions