如何在 Oracle 临时表上使用 DML 而不产生大量撤消日志
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2430490/
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 use DML on Oracle temporary table without generating much undo log
提问by Sambath Prum
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?
使用 Oracle 临时表不会像普通表一样生成太多重做日志。但是，仍然会生成撤消日志。因此，如何在临时表上编写插入、更新或删除语句，但 Oracle 不会生成撤消日志或生成尽可能少的日志？
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/?
此外，在插入语句中使用 / +append/ 会生成很少的撤消日志。我对么？如果没有，任何人都可以向我解释使用提示 / +append/ 吗？
INSERT /*+APPEND*/ INTO table1(...) VALUES(...);
回答by APC
Oracle needs UNDO information to rollback the DML in the transaction. As Gary puts it in his comment:
Oracle 需要 UNDO 信息来回滚事务中的 DML。正如加里在他的评论中所说：
"The UNDO is needed to rollback the effects of a single statement if it fails partway through. It is also needed to provide for a ROLLBACK TO SAVEPOINT or a ROLLBACK (though for GLOBAL TEMPORARY TABLES the latter would only be relevant for session duration GTTs)."
“如果单个语句中途失败，则需要 UNDO 来回滚单个语句的效果。还需要提供 ROLLBACK TO SAVEPOINT 或 ROLLBACK（尽管对于 GLOBAL TEMPORARY TABLES，后者仅与会话持续时间 GTT 相关） .”
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.
此 UNDO 信息本身会生成 REDO。对于这种情况，您无能为力：临时表需要 UNDO，这就是它的结束。
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.
尽量减少 UNDO 的数量非常简单：只需插入记录和选择记录。INSERT 生成最少数量的 UNDO，因为回滚 INSERT 只需要 rowid。相反，DELETE 语句生成最多的 UNDO，因为数据库必须存储整个记录。基本上，要回滚 INSERT 发出 DELETE，回滚 DELETE 发出 INSERT。UPDATE 生成可变数量的 UNDO，因为我们需要更改列的旧版本；更改的列越多，它们越大，生成的 UNDO 量就越大。
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.
在会话一中，用户将向临时表中插入大量记录，然后将其删除。在会话 2 中，DBA 将监视事务的 UNDO 使用情况。
SSN1> insert into gtt23
2 select * from big_table
3 /
553928 rows created.
SSN1>
Undo usage:
撤销用法：
SSN2> select space, noundo, used_ublk, used_urec from v$transaction
2 /
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 257 10816
SSN2>
Now the deletion:
现在删除：
SSN1> delete from gtt23
2 /
553928 rows deleted.
SSN1>
Undo usage (several samples during a long running statement)::
撤消使用（长时间运行的语句中的几个示例）::
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 11123 435605
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 13413 525452
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 14552 570567
SSN2>
Commit (the temporary table has transaction scope i.e. DELETE ROWS)
提交（临时表具有事务范围，即 DELETE ROWS）
SSN1> commit
2 /
Commit complete.
SSN1>
Undo usage:
撤销用法：
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
no rows selected
SSN2>
The undo usage is accumulative:
撤消使用是累积的：
SSN1> insert into gtt23
2 select * from big_table
3 /
553928 rows created.
SSN1> delete from gtt23
2 /
553928 rows deleted.
SSN1> insert into gtt23
2 select * from big_table
3 /
553928 rows created.
SSN1>
Undo usage
撤消使用
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 258 10816
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 14766 579495
SSN2> r
1* select space, noundo, used_ublk, used_urec from v$transaction
SPA NOU USED_UBLK USED_UREC
--- --- ---------- ----------
NO NO 14819 581685
SSN2>
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.
因此，为了尽量减少临时表生成的 UNDO 的影响，请确保插入正确的数据，一次。避免对其应用更新，尤其是避免从中删除大量记录。如果您使用的是具有事务范围的临时表，则确实不需要从中删除记录。如果您的临时表具有会话持续时间并且您需要将其清除，则最好使用 TRUNCATE（如果可能）而不是 DELETE。
回答by Vincent Malgrat
Found this on AskTom:
在AskTom上找到了这个：
conventional path inserts generate UNDO. they have to, you need to be able to rollback, you need to be able to support multi-versioning.
UNDO is always protected by redo.
If you direct path the global temporary table ( insert /*+ APPEND */) you can bypass undo ON THE TABLE - but not on the indexes. Hence you can reduce (marginally typically as it is usually indexes that generate the most undo) the amount of redo, but you cannot eliminate it.
常规路径插入生成 UNDO。他们必须，你需要能够回滚，你需要能够支持多版本。
UNDO 始终受重做保护。
如果您直接路径全局临时表（insert /*+ APPEND */），您可以绕过对表的撤消 - 但不能在索引上。因此，您可以减少（通常是有限的，因为它通常是生成最多撤消的索引）重做的数量，但您无法消除它。
回答by EvilTeach
I believe you may also get some benefit from the NOLOGGINGkeyword.
我相信您也可以从NOLOGGING关键字中获得一些好处。
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.
附加提示建议 Oracle 应该使用 DIRECT-PATH 操作，这可以加快插入速度。如果我没记错的话，您应该可以独占访问该表。在插入后提交很重要，以便您可以从中选择信息。
