oracle 如何提高SQL插入查询的性能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25364004/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to improve the SQL insert query performance?
提问by Amit Singh
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:
从一个表中查询数据（大约 8300 万行）并将其插入到同一架构中的另一个表中时，我的响应时间很长。大约需要一个半小时。查询如下所示：
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
回答by neshkeev
- You can try to use the APPENDhint 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
- 您可以尝试使用APPEND提示让 oracle 通过 HWM 标记写入新数据。
- 如果您有索引，oracle 会浪费一些时间来更新它们。您还可以禁用它们并在插入完成后重建。
- 此外，如果有 8300 万行，那么插入会生成大量的 REDO 信息。要禁用表的重做日志生成，请使用nologging 选项：
alter table YOUR_TABLE nologging; - 您也可以使用异步写入您的在线重做日志
commit_wait，commit_logging tips - 您可以设置作业队列以在后台安排长时间运行的操作。阅读更多使用这个
- 您可以使用并行 DML
This is all the suggestions I can give you without having the explain plan.
这是我可以在没有解释计划的情况下给你的所有建议。
