Oracle 全部插入与插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19559418/
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
Oracle Insert All vs Insert
提问by Abhishek Sanghvi
In Oracle I came across two types of insert statement
在 Oracle 中，我遇到了两种类型的插入语句
1) Insert All: Multiple entries can be inserted using a single sql statement
1）Insert All：可以使用单个sql语句插入多个条目
2) Insert : One entry will be updated per insert.
2) 插入：每个插入将更新一个条目。
Now I want to insert around 100,000 records at a time. (Table have 10 fields with includes a primary key). I am not concerned about any return value.
现在我想一次插入大约 100,000 条记录。（表有 10 个字段，其中包含一个主键）。我不关心任何返回值。
I am using oracle 11g.
我正在使用 oracle 11g。
Can you please help me with respect to performancewhich is better "Insert" or "Insert All".
你能帮帮我对于性能哪个更好“插入”或“全部插入”。
回答by jdmneon
I know this is kind of a Necro but it's pretty high on the google search results so I think this is a point that worth making.
我知道这有点像死灵，但它在谷歌搜索结果中的排名很高，所以我认为这是一个值得提出的观点。
Insert All can give dramatic performance benefits if you are building a web application because it is a single SQL statement that requires only one round trip to your database. In most cases although far from all cases. the majority of the cost of a query is actually latency. Depending on what framework you are using, this syntax can help you avoid unnecessary round trips.
如果您正在构建 Web 应用程序，Insert All 可以带来显着的性能优势，因为它是一条 SQL 语句，只需要一次往返数据库。在大多数情况下，尽管远非所有情况。查询的大部分成本实际上是延迟。根据您使用的框架，此语法可以帮助您避免不必要的往返。
This might seem incredibly obvious but I have seen many, many production web applications in large companies that have forgotten this simple fact.
这可能看起来非常明显，但我已经看到大公司中的许多生产 Web 应用程序都忘记了这个简单的事实。
回答by Nick Krasnov
Insertstatement and insert allstatement are practically the same conventional insertstatement. insert all, which has been introduced in 9i version simply allows you to do insertion into multiple tables using one statement. Another type of insert that you could use to speed up the process is direct-path insert - you use /*+ append*/or /*+ append_values*/(Oracle 11g) hints
Insertstatement 和insert allstatement 实际上是相同的常规insert语句。insert all，它已在 9i 版本中引入，仅允许您使用一条语句插入多个表。另一种可用于加快进程的插入类型是直接路径插入 - 您使用/*+ append*/or /*+ append_values*/(Oracle 11g) 提示
insert /*+ append*/ into some_table(<<columns>>)
select <<columns or literals>>
from <<somwhere>>
or (Oracle 11g)
或（甲骨文 11g）
insert /*+ append_values*/ into some_table(<<columns>>)
values(<<values>>)
to tell Oracle that you want to perform direct-path insert. But, 100K rows it's not that many rows and conventional insertstatement will do just fine. You wont get significant performance advantage using direct-path insert with that amount of data. Moreover direct-path insert wont reuse free space, it adds new data after HWM(high water mark), hence require more space. You wont be able to use selectstatement or other DML statement, if you has not issued commit.
告诉 Oracle 您要执行直接路径插入。但是，100K 行并不是那么多行，常规insert语句就可以了。使用具有该数量数据的直接路径插入不会获得显着的性能优势。此外，直接路径插入不会重用空闲空间，它会在 HWM（高水位线）之后添加新数据，因此需要更多空间。您将无法使用select语句或其它DML语句，如果您还没有出台commit。
回答by Sunil Khatri
To use FORALL you would need PLSQL tables. This process is quite fast.
要使用 FORALL，您需要 PLSQL 表。这个过程相当快。
You can also choose the table to have NO LOG option which would speed the process up during inserts.
您还可以选择具有 NO LOG 选项的表，这将加快插入过程中的进程。
