在INSERT和UPDATE操作中如何以及何时使用索引? [英] How and when are indexes used in INSERT and UPDATE operations?

问题描述

考虑此有关索引的Oracle文档，关于插入和的此有关在StackOverflow上的问题，我得出的结论是:

Consider this Oracle docs about indexes, this about speed of insert and this question on StackOverflow lead me to conclusion that:

• 索引可帮助我们更快地找到信息
• 主键和唯一键会自动编入索引
• 插入索引可能会导致性能变差

但是，每次讨论索引时，仅显示SELECT操作作为示例.

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

我的问题是:INSERT和UPDATE操作中是否使用了索引?什么时候以及如何?

My question is: are indexes used in INSERT and UPDATE operations? When and how?

我的建议是:

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

• UPDATE can use index in WHERE clause (if the column in the clause has index)
• INSERT can use index when uses SELECT (but in this case, index is from another table)
• or probably when checking integrity constraints

但是我对使用索引并不了解.

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

推荐答案

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

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.

对于INSERT语句，尽管INSERT本身不需要索引，但是索引也需要插入"，因此需要由oracle访问. INSERT可以导致使用索引的另一种情况是这样的INSERT:

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 INTO mytable (mycolmn)
SELECT mycolumn + 10 FROM mytable;

这篇关于在INSERT和UPDATE操作中如何以及何时使用索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！