oracle 在 INSERT 和 UPDATE 操作中如何以及何时使用索引？

How and when are indexes used in INSERT and UPDATE operations?

提问by Ivanka Eldé

Consider thisOracle docs about indexes, thisabout speed of insert and thisquestion on StackOverflow lead me to conclusion that:

考虑这个关于索引的 Oracle 文档，这个关于插入速度和这个关于 StackOverflow 的问题让我得出结论：

• Indexes helps us locate information faster
• Primary and Unique Keys are indexed automatically
• Inserting with indexes can cause worse performance

• 索引帮助我们更快地定位信息
• 自动索引主键和唯一键
• 使用索引插入会导致性能下降

However every time indexes are discussed there are only SELECToperations shown as examples.

然而，每次讨论索引时，都只SELECT显示了作为示例的操作。

My question is: are indexes used in INSERTand UPDATEoperations? When and how?

我的问题是：索引是否用于INSERT和UPDATE操作？何时以及如何？

My suggestions are:

我的建议是：

• UPDATEcan use index in WHEREclause (if the column in the clause has index)
• INSERTcan use index when uses SELECT(but in this case, index is from another table)
• or probably when checking integrity constraints

• UPDATE可以在WHERE子句中使用索引（如果子句中的列有索引）
• INSERT使用时可以使用索引SELECT（但在这种情况下，索引来自另一个表）
• 或者可能在检查完整性约束时

but I don't have such deep knowledge of using indexes.

但我对使用索引没有那么深入的了解。

回答by Vampiro

For UPDATE statements, index can be used by the optimiser if it deems the index can speed it up. The index would be used to locate the rows to be updated. The index is also a table in a manner of speaking, so if the indexed column is getting updated, it obviously needs to UPDATE the index as well. On the other hand if you're running an update without a WHERE clause the optimiser may choose not to use an index as it has to access the whole table, a full table scan may be more efficient (but may still have to update the index). The optimiser makes those decisions at runtime based on several parameters such as if there are valid stats against the tables and indexes in question, how much data is affected, what type of hardware, etc.

对于 UPDATE 语句，如果优化器认为索引可以加快速度，则可以使用索引。索引将用于定位要更新的行。索引从某种意义上说也是一个表，所以如果索引列正在更新，它显然也需要更新索引。另一方面，如果您在没有 WHERE 子句的情况下运行更新，优化器可能会选择不使用索引，因为它必须访问整个表，全表扫描可能更有效（但可能仍然需要更新索引）。优化器在运行时根据几个参数做出这些决定，例如是否存在针对相关表和索引的有效统计信息、受影响的数据量、硬件类型等。

For INSERT statements though the INSERT itself does not need the index, the index will also need to be 'inserted into', so will need to be accessed by oracle. Another case where INSERT can cause the index to be used is an INSERT like this:

对于 INSERT 语句，虽然 INSERT 本身不需要索引，但索引也需要“插入”，因此需要由 oracle 访问。INSERT 可以导致使用索引的另一种情况是这样的 INSERT：

INSERT INTO mytable (mycolmn)
SELECT mycolumn + 10 FROM mytable;

回答by Shariar Imtiaz

Insert statement has no direct benefit for index. But more index on a table cause slower insert operation. Think about a table that has no index on it and if you want to add a row on it, it will find table block that has enough free space and store that row. But if that table has indexes on it database must make sure that these new rows also found via indexes, So to add new rows on a table that has indexes, also need to entry in indexes too. That multiplies the insert operation. So more index you have, more time you need to insert new rows.

Insert 语句对索引没有直接的好处。但是more index on a table cause slower insert operation。想想一个没有索引的表，如果你想在上面添加一行，它会找到有足够空闲空间的表块并存储该行。但是如果该表有索引，数据库必须确保这些新行也通过索引找到，所以要在有索引的表上添加新行，也需要输入索引。这乘以插入操作。So more index you have, more time you need to insert new rows.

For update it depends on whether you update indexed column or not. If you are not updating indexed column then performance should not be affected. Index can also speed up a update statements if the where conditions can make use of indexes.

对于update it depends on whether you update indexed column or not. 如果您不更新索引列，则不应影响性能。Index can also speed up a update statements if the where conditions can make use of indexes.