Update


An update statement must relocate the changed index entries to maintain the index order. For that, the database must remove the old entry and add the new one at the new location. The response time is basically the same as for the respective delete and insert statements together.

The update performance, just like insert and delete, also depends on the number of indexes on the table. The only difference is that update statements do not necessarily affect all columns because they often modify only a few selected columns. Consequently, an update statement does not necessarily affect all indexes on the table but only those that contain updated columns.

Figure 8.3 shows the response time for two update statements: one that sets all columns and affects all indexes and then a second one that updates a single column so it affects only one index.

Figure 8.3. Update Performance by Indexes and Column Count


The update on all columns shows the same pattern we have already observed in the previous sections: the response time grows with each additional index. The response time of the update statement that affects only one index does not increase so much because it leaves most indexes unchanged.

To optimize update performance, you must take care to only update those columns that were changed. This is obvious if you write the update statement manually. ORM tools, however, might generate update statements that set all columns every time. Hibernate, for example, does this when disabling the dynamic-update mode. Since version 4.0, this mode is enabled by default.

When using ORM tools, it is a good practice to occasionally enable query logging in a development environment to verify the generated SQL statements. The tip entitled “Enabling SQL Logging” has a short overview of how to enable SQL logging in some widely used ORM tools.

If you like my way of explaining things, you’ll love my book.

Think About It

Can you think of a case where insert or delete statements do not affect all indexes of a table?

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

3
votes
2
answers
259
views

pagination with nulls

14 hours ago Markus Winand ♦♦ 771
pagination
2
votes
1
answer
1.9k
views
0
votes
2
answers
1.1k
views

different execution plans after failing over from primary to standby server

Sep 17 at 11:46 Markus Winand ♦♦ 771
oracle index update