oracle 为什么使用 INSERT /*+APPEND*/ ？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23823048/
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
Why INSERT /*+APPEND*/ is used?
提问by Mithun Khatri
A java program is doing bulk insertion into the Oracle table. It works fine for 100-200 records but its hanging for more than 4000 records.
一个 java 程序正在对 Oracle 表进行批量插入。它适用于 100-200 条记录，但它挂起 4000 多条记录。
When I checked the query, its having INSERT /*+APPEND*/in it.
当我检查查询时，它包含INSERT /*+APPEND*/在其中。
What is INSERT /*+APPEND*/and why is it used in INSERT queries? Is the program hanging because of this thing?
INSERT /*+APPEND*/它是什么 以及为什么在 INSERT 查询中使用它？程序是不是因为这个东西挂了？
采纳答案by ibre5041
It's a SQL?optimizer hint. In your case most likely it has NO?impact. Maybe it's a premature optimization.
这是一个 SQL?优化器提示。在您的情况下，它很可能没有影响。也许这是一个过早的优化。
This hint should enforce so called direct path insert, which bypasses Oracle's buffer cache and writes data directly into data-files. Data are appended beyond high water mark(HWM) - ignoring table's free space map, no triggers are fired and no constraints are checked. On the other hand this type of insert is blocking. Only one session can use it on particular table at the same time.
这个提示应该强制执行所谓的直接路径插入，它绕过 Oracle 的缓冲区缓存并将数据直接写入数据文件。数据附加到高水位线 (HWM) 之外 - 忽略表的可用空间映射，不触发触发器并且不检查约束。另一方面，这种类型的插入是阻塞的。只有一个会话可以同时在特定的桌子上使用它。
An excerpt from docs:
文档摘录：
"The APPEND hint is only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint" This hint only works when you use INSERT as SELECT statement
"APPEND 提示仅支持 INSERT 语句的子查询语法，不支持 VALUES 子句。如果您使用 VALUES 子句指定 APPEND 提示，它将被忽略并使用常规插入。使用直接路径 INSERT 和VALUES 子句，参考“APPEND_VALUES 提示” 这个提示只在你使用 INSERT 作为 SELECT 语句时有效
insert into <table> SELECT * FROM ....
When you insert values Oracle silently ignores it. Newer Oracle versions also support APPEND_VALUES hint.
当您插入值时，Oracle 会默默地忽略它。较新的 Oracle 版本也支持 APPEND_VALUES 提示。
If you want to validate the hint being used open Toad or SQL Developer, select session browser, find that particular session and it's current SQL and exec plan. When you see in the exec plan something like "INSERT into TABLE CONVENTIONAL"then the hint is ignored. If you see "INSERT as SELECT"then you are using direct path load.
如果要验证正在使用的提示，请打开 Toad 或 SQL Developer，选择会话浏览器，找到该特定会话及其当前 SQL 和执行计划。当您在 exec 计划中看到类似"INSERT into TABLE CONVENTIONAL"的内容时，提示将被忽略。如果您看到，"INSERT as SELECT"那么您正在使用直接路径加载。
回答by Mureinik
inserttypically looks for the first empty space in your table to add the new record. While this conserves space, it may sometime slow down the operation.
insert通常会查找表中的第一个空白空间以添加新记录。虽然这可以节省空间，但有时可能会减慢操作速度。
/*+APPEND*/is a hintwhich causes the insertstatement to always, well, for lack of a better term, append the newly inserted row at the end of the table. This may waste some space, but is usually faster. It's especially useful if you know you don't have too many empty regions in the middle of the table (i.e., you don't perform a lot of deletes and updates on it).
/*+APPEND*/是一个提示，它导致insert语句总是，好吧，由于缺乏更好的术语，将新插入的行附加到表的末尾。这可能会浪费一些空间，但通常更快。如果您知道表中间没有太多空白区域（即，您没有对其执行大量deletes 和updates），它会特别有用。
回答by A Nice Guy
It is a ORACLE compliler hint. It is there for a purpose and is not a comment. This hint is used there to speed up insertions, so I don't think it is the cause for hanging up the program.
这是一个 ORACLE 编译器提示。它是有目的的，不是评论。这个提示是用来加速插入的，所以我不认为这是挂断程序的原因。
However, please ask your dba to check available free space in the tablespace this table is using.(Your dba will understand this statement better :) )
但是，请让您的 dba 检查此表正在使用的表空间中的可用空间。（您的 dba 会更好地理解此语句:)）
There might be a issue that there is very less space available for any more insertions in that tablespace, which the dba should be able to resolve.
可能存在一个问题，即该表空间中可用于更多插入的空间非常少，dba 应该能够解决这个问题。
Why there could be less space available? because the APPEND hint wastes space as explained by above answer by @Mureinik, and can be a problem if there are too many frequent insertions with this hint in that table.
为什么可用空间会变少？因为 APPEND 提示会浪费空间，正如@Mureinik 的上述回答所解释的那样，并且如果该表中带有此提示的频繁插入过多，则可能会出现问题。
回答by SQL PLSQL
/*+ APPEND */
/*+ 追加 */
- called direct path insert.
- Data are appended beyond high water mark(HWM) - ignoring table's free space map, no triggers are fired and no constraints are checked.
- Only one session can use it on particular table at the same time
- 称为直接路径插入。
- 数据附加到高水位线 (HWM) 之外 - 忽略表的可用空间映射，不触发触发器并且不检查约束。
- 只有一个会话可以同时在特定的桌子上使用它
If you want to check the hint being used then find that particular session and its current SQL and exec plan. When you see in the exec plan something like? "INSERT into TABLE CONVENTIONAL"? then the hint is ignored. If you see? "INSERT as SELECT" then you are using direct path load
如果您想检查正在使用的提示，请找到该特定会话及其当前的 SQL 和 exec 计划。当你在执行计划中看到类似的东西时？“插入常规表”？然后提示被忽略。如果你看到？“INSERT as SELECT”然后您使用的是直接路径加载
- only supported with the subquery syntax of the INSERT statement, not the VALUES clause.
- If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used.
- To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint" This hint only works when you use INSERT as SELECT statement.
- 仅支持 INSERT 语句的子查询语法，不支持 VALUES 子句。
- 如果您使用 VALUES 子句指定 APPEND 提示，它将被忽略并使用常规插入。
- 要将直接路径 INSERT 与 VALUES 子句一起使用，请参阅“APPEND_VALUES 提示” 此提示仅在您将 INSERT 用作 SELECT 语句时有效。
回答by vapcguy
I don't know about "speeding up" the insert, but I've definitely found the hint, and yes, this is what Oracle calls it - not a comment - extremely useful for maintaining the order of records when I do an insert from one table into another, when I pair the command with an ORDER BYat the end:
我不知道“加速”插入，但我确实找到了提示，是的，这就是 Oracle 所说的 - 不是评论 - 当我从一张桌子变成另一张桌子，当我将命令与最后的 an 配对ORDER BY时：
INSERT /*+ append */ INTO MYAPP.COUNTRIES (ID, CODE, NAME)
SELECT ID, CODE, NAME FROM MYAPP.OLD_COUNTRIES_TABLE ORDER BY NAME ASC;
COMMIT;
I've gotten flack that this is just a coincidence (see this threadon S.O.), but I've used it many times over, now, and that's at least the fruit I'm ending up with. If you use INSERTwith ORDER BY, but withoutthe append hint, the ORDER BYgets ignored and is utterly useless for ensuring the records can then be retrieved according to the order in which they were inserted (see my proof in my answer, here).
我觉得这只是一个巧合（请参阅SO 上的这个帖子），但我现在已经多次使用它，这至少是我最终得到的果实。如果您使用INSERTwith ORDER BY，但没有append 提示，ORDER BY则会被忽略，并且对于确保可以根据插入顺序检索记录完全没有用（请参阅我的答案中的证明，这里）。
