7

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.

Community
  • 1
  • 1
Ivanka Eldé
  • 228
  • 1
  • 2
  • 12
  • 2
    Indexes are used with INSERT and UPDATE operations if the table has foreign keys or other types of constraints, unique for example. You can even have an infamous TM - contention event if you don't have an index on a foreign key. Oracle uses indexes on dependent tables too, not only on the table that you are working with. There are tons of materials in the Internet and documentation so I don't think there's a point of discussing it here. – stee1rat Jan 10 '16 at 13:52
  • Be careful, one of your links (https://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-speed.html specifically) is for mysql, not Oracle. Docs for both databases are located on docs.oracle.com, it's easy to confuse them. – Timekiller Jan 10 '16 at 13:56
  • Thank for your answers. I knew that indexes are not used only on the tables I am working with. Probably I was expecting something more difficult behind . :) – Ivanka Eldé Jan 10 '16 at 14:00
  • The word is indices not indexes. – DwB Jan 10 '16 at 16:31
  • 1
    @DwB see http://english.stackexchange.com/questions/61080/plural-of-index-indexes-or-indices both are valid in English, although there is a preference for `indices` in technical areas – Paul Maxwell Jan 11 '16 at 02:20
  • Oracle uses 'indexes', which should be perfectly fine for an Oracle question, see https://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN016 – Vampiro Jan 11 '16 at 09:03
  • @Used_By_Already consider learning English. The plural form of the noun index is indices. The present tense of the verb form of index is indexes (as in "Bob produces an index of that book", or "Bob indexes that book") – DwB Jan 12 '16 at 13:58
  • @DwB do consider reading the content referred to. As it happens I prefer indices also, but I do not have the final say in a language that does not remain static. – Paul Maxwell Jan 12 '16 at 14:48

2 Answers2

3

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;
Vampiro
  • 335
  • 4
  • 15
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.

Shariar Imtiaz
  • 483
  • 1
  • 5
  • 15