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

Question

Consider this Oracle docs about indexes, this about insertion speed, and this StackOverflow question leads me to conclude that:

• Indexes help you find information faster.
• Primary and unique keys are indexed automatically
• Index insertion can lead to poor performance

However, each time indexes are discussed, only SELECT operations are shown as examples.

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

My suggestions:

• UPDATE can use an index in WHERE (if the column in the index has an index)
• INSERT can use an index when using SELECT (but in this case an index from another table)
• or perhaps when checking integrity constraints

but I don’t have such deep knowledge about using indexes.

Answer 1

For UPDATE statements, an index can be used by the optimizer if it believes that the index can speed it up. The index will be used to search for updated rows. The index is also a table in the form of speech, so if the indexed column is updated, it is obviously necessary to also UPDATE the index. On the other hand, if you use an update without a WHERE clause, the optimizer may not use the index because it must access the entire table, a full table scan may be more efficient (but it may still be necessary to update the index). The optimizer makes these decisions at run time based on several parameters, for example, if there are valid statistics against the tables and indexes in question, how much data is affected, what type of equipment, etc.

For INSERT statements, although INSERT itself does not need an index, the index must also be "inserted in", so oracle must be addressed to it. Another case where INSERT can lead to the use of an index is INSERT, for example:

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

Answer 2

Inserting an instruction has no direct benefit to the index. But more index on a table cause slower insert operation . Think of a table that does not have an index, and if you want to add a row to it, it will find a block of the table that has enough free space and save that row. But if there are indexes in this table, then in the database you need to make sure that these new rows are also found through the indexes. Therefore, to add new rows to a table with indexes, it is also necessary to enter indexes. This 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 . Unless you are updating an indexed column, performance should not be affected. Index can also speed up a update statements if the where conditions can make use of indexes .