想在 Oracle Delete 语句上启用 PARALLEL 和 NOLOGGING
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32196095/
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
Want to enable PARALLEL and NOLOGGING on Oracle Delete statement
提问by DjBuddy
I have a very huge table with more than 3.5 billion records that increases 500K each day, I want to delete all records before year 2014. Every delete command I run, after few hours fall into error state, so I am looking at doing this job faster, last command I run was:
我有一个非常大的表，有超过 35 亿条记录，每天增加 50 万条，我想删除 2014 年之前的所有记录。我运行的每个删除命令，几个小时后都会陷入错误状态，所以我正在考虑做这项工作更快，我运行的最后一个命令是：
DELETE /*+ PARALLEL (8) */ FROM XYZ WHERE YEAR <= 2014;
After 744 minutes I get
744分钟后我得到
ORA-12801: error signaled in parallel query server
ORA-12801: 在并行查询服务器中发出错误信号
I guess if I could run the DELETE command with both PARALLEL and NOLOGGING, switch maybe I could do this, but I don't know how to run the session in NOLOGGING state and at the same time enable PARALLEL for the command, also I know we can run
我想如果我可以同时使用 PARALLEL 和 NOLOGGING 运行 DELETE 命令，切换也许我可以这样做，但我不知道如何在 NOLOGGING 状态下运行会话并同时为命令启用 PARALLEL，我也知道我们可以跑
DELETE /*+ NOLOGGING PARALLEL (8) */ FROM XYZ WHERE YEAR <= 2014;
But as I find in somewhere, it seems that with this command PARALLEL hint will be ignored.
但正如我在某处发现的那样，似乎使用此命令 PARALLEL 提示将被忽略。
Please advise on how to run the DELETE command both in PARALLEL and NOLOGGING
请告知如何在 PARALLEL 和 NOLOGGING 中运行 DELETE 命令
回答by Florin Ghita
Not logging the delete operation won't help too much. There is really small amount of things to be redone for a delete operation.
不记录删除操作不会有太大帮助。删除操作需要重做的事情真的很少。
The problem here is a huge amount of undo(It contains every row deleted in order to be inserted in case of error/rollback). Also, parallel just speed up things, don't change the amount of undo.
这里的问题是大量的撤消（它包含删除的每一行，以便在错误/回滚的情况下插入）。另外，并行只是加快速度，不要改变撤销的数量。
I see here two solutions/workarounds:
我在这里看到两个解决方案/解决方法：
increase the undo tablespace.(talk with your dba)
delete in smaller chunks.(for example, delete 4 months, commit, then again 4 months, commit again, etc.)
增加撤消表空间。（与您的 dba 交谈）
以较小的块删除。（例如，删除 4 个月，提交，然后再次 4 个月，再次提交等）
UPDATE: Now I'm a little unsure about my statement above about redo. Because writing in undo willgenerate redo. However, the longest part of the execution of your delete is the rollback because you hit an exception(it may be 2/3 of the time). Splitting the task in 3-4 chunks may be faster than your failed query.
更新：现在我有点不确定我上面关于重做的声明。因为在undo中写入会产生redo。但是，执行删除的最长部分是回滚，因为您遇到了异常（可能是 2/3 的时间）。将任务分成 3-4 个块可能比失败的查询更快。
