如何在Oracle临时表上使用DML而不生成太多undo log
使用 Oracle 临时表不会像普通表那样生成太多重做日志。但是，仍然会生成撤消日志。那么，如何在临时表上写insert、update、delete语句，Oracle却不会生成undo log或者尽量少生成undo log呢？
而且，在插入语句中使用/+append/将产生很少的undo日志。我说得对吗？如果没有，有人可以解释一下如何使用提示 /+append/ 吗？
INSERT /*+APPEND*/ INTO table1(...) VALUES(...);
Using an Oracle temporary table does not generate much redo log as a normal table. However, the undo log is still generated. Thus, how can I write insert, update, or delete statement on a temporary table but Oracle will not generate undo log or generate as little as possible?
Moreover, using /+append/ in the insert statement will generate little undo log. Am I correct? If not, could anyone explain me about using the hint /+append/?
INSERT /*+APPEND*/ INTO table1(...) VALUES(...);
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Oracle需要UNDO信息来回滚事务中的DML。正如加里在评论中所说：
这个 UNDO 信息本身会生成 REDO。对于这种情况你无能为力：临时表需要 UNDO，这就是它的结束。
要最小化 UNDO 的数量非常简单：只需插入记录并选择记录。 INSERT 生成最少的 UNDO，因为回滚 INSERT 只需要 rowid，相反，DELETE 语句生成最多的 UNDO，因为数据库必须存储整个记录，要回滚 INSERT 需要发出 DELETE，而要回滚 DELETE。发出 INSERT 会生成可变数量的 UNDO，因为我们需要更改的列的旧版本；更改的列越多且越大，生成的 UNDO 数量就越大
。
在会话一中，用户将向临时表中插入大量记录，然后删除它们。 在会话二中，DBA 将监视事务的撤消使用情况：
现在
删除：
撤消使用情况（长时间运行语句期间的几个示例）。 ::
提交（临时表具有事务范围，即 DELETE ROWS）
撤消使用：
撤消使用是累积：
撤消使用
摘要
因此，为了最大限度地减少撤消的影响，临时表生成确保您一次性插入正确的数据。避免对其应用更新，尤其是避免从中删除大量记录。如果您使用具有事务范围的临时表，则实际上不需要从中删除记录。如果您的临时表有会话持续时间并且您需要将其清除，那么如果可能的话，最好使用 TRUNCATE，而不是 DELETE。
Oracle needs UNDO information to rollback the DML in the transaction. As Gary puts it in his comment:
This UNDO information itself generates REDO. There is nothing you can do about this situation: temporary tables need UNDO and that's the end of it.
To minimize the amount of UNDO is quite simple: just insert records and select records. INSERT generates the smallest amount of UNDO, because rolling back an INSERT requires simply the rowid. Conversely DELETE statements generate the most UNDO, because the database has to store the entire record. Basically, to rollback an INSERT issue a DELETE, to rollback a DELETE issue an INSERT. An UPDATE generates a variable amount of UNDO, because we need the old versions of the changed columns; the more columns changed and the bigger they are, the larger the amount of UNDO generated.
Demonstration
In session one a user will insert a lot of records into a temporary table and then delete them. In session two a DBA will monitor the transaction's UNDO usage.
Undo usage:
Now the deletion:
Undo usage (several samples during a long running statement)::
Commit (the temporary table has transaction scope i.e. DELETE ROWS)
Undo usage:
The undo usage is accumulative:
Undo usage
Summary
So, to minimise the impact of UNDO which a temporary table generates make sure you insert the right data, once. Avoid applying updates to it and especially avoid deleting large numbers of records from them. If you are using a temporary table with a transaction scope there really should be no need to delete records from it. If your temporary table has a session duration and you need to clear it out, it would be better to use TRUNCATE, if possible, rather than DELETE.
发现这个 AskTom< /a>:
Found this on AskTom:
我相信您也可以从 NOLOGGING 关键字。
追加提示建议 Oracle 应使用 DIRECT-PATH 操作，这可以加快插入速度。如果我没记错的话，您应该拥有对该表的独占访问权限。插入后提交很重要，以便您可以从中选择信息。
I believe you may also get some benefit from the NOLOGGING keyword.
The append hint suggests that Oracle should use DIRECT-PATH operations, which can result in faster inserts. If I recall correctly you should have exclusive access to the table. It is important to commit after the insert, so that you can select information from it.