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

Question

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

• 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 SELECT operations shown as examples.

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

My suggestions are:

• 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.

Solution 1

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.

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;

Solution 2

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.

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.