I'm trying to understand the sequence of events in inserts where both an index and a transaction are involved.

For example, Oracle documentation states:

If you create [or have] one or more indexes before loading data, the database then must update every index as each row is inserted.

But what happens if I create a transaction, insert five rows, then commit? Are indexes updated for every insert, or just at the commit point?

Logic tells me that they would only be updated at the commit point, because an updated index couldn't possibly be of use until those records are committed. But is that true?

If so, when I have 1m rows to insert, for best performance I should do one large commit of all rows and not 10 transactions of 100k records? Of course I realize this risks a greater rollback if row 999,999 fails.

Apologies if my terminology is a little out. I'm not a DBA by trade. I'm not so much interested in a particular database, as databases in general, although Oracle and Postgres are what I use most. I've searched on this topic but can't really find a definitive answer.

2 Answers 2


I work with SQL Server and Oracle. There are probably some exceptions, but for those platforms the general answer is that data and indexes will be updated at the same time.

I think that it would be helpful to draw a distinction between when the indexes are updated for the session that owns the transaction and for other sessions. By default, other sessions will not see the updated indexes until the transaction is committed. However, the session that owns the transaction will immediately see the updated indexes.

For one way to think about it, consider at a table with a primary key. In SQL Server and Oracle this is implemented as an index. Most of the time we want there to immediately be an error if an INSERT is done which would violate the primary key. For that to happen the index must be updated at the same time as the data. Note that other platforms, such as Postgres, allow deferred constraints which are checked only when the transaction is committed.

Here's a quick Oracle demo showing a common case:


INSERT INTO X_TABLE VALUES (1); -- no commit

The second INSERT statement throws an error:

SQL Error: ORA-00001: unique constraint (XXXXXX.SYS_C00384850) violated

00001. 00000 - "unique constraint (%s.%s) violated"

*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.

*Action: Either remove the unique restriction or do not insert the key.

If you prefer to see an index update action below is a simple demo in SQL Server. First create a two column table with one million rows and a nonclustered index on the VAL column:




-- insert one million rows with N from 1 to 1000000
SELECT N, N FROM dbo.Getnums(1000000);

The following query can use the nonclustered index because the index is a covering index for that query. It contains all of the data needed to execute it. As expected no returns are returned.


query 1

Now let's start a transaction and update VAL for almost all of the rows in the table:


WHERE ID <> 1;

Here is part of the query plan for that:

query 2

Circled in red is the update to the nonclustered index. Circled in blue is the update to the clustered index, which is essentially the table's data. Even though the transaction has not been committed we see that the data and the index is updated in part of the query's execution. Note that you will not always see this in a plan depending on the size of data involved along with possibly other factors.

With the transaction still not committed, let's revisit the SELECT query from above.


enter image description here

The query optimizer is still able to use the index and this time it estimates that 999999 rows will be returned. Executing the query returns the expected result.

That was a simple demo but hopefully it cleared things up a bit.

As an aside, I am aware of a few cases in which it could be argued that an index is not immediately updated. This is done for performance reasons and the end user should not be able to see inconsistent data. For example, sometimes deletes will not be fully applied to an index in SQL Server. A background process runs and eventually cleans up the data. You can read about ghost records if you're curious.

  • That's a super answer - and also answers another thing I was wondering: whether a primary key (or similar) violation would occur on Insert or on Commit. Thanks for such a complete response. Mar 2, 2017 at 15:41
  • The related question (about when a constraint violation will occur) is related to whether you use deferred transactions or not. SQL Server for example, has not implemented deferred transaction, so all violations occur at the end of statements. Other DBMS have (Postgres for example, although not for all types of constraints), so when you a constraint is deferred, the violation will be checked at the commit phase of the transaction). Mar 2, 2017 at 16:53
  • Oracle also supports deferred constraints
    – BobC
    Mar 5, 2017 at 19:07
  • "By default, other sessions will not see the updated indexes until the transaction is committed". Is this why some deadlocks can be avoided by creating an index containing the needed data for a select for example? because the session won't see the updates on the index until the transaction is committed by the other session, and SQL won't look at the table (lock) because the data needed is on the index itself?
    – Alex
    Jan 16, 2023 at 17:59

My experience is that 1,000,000 row insert will actually require more resources and take longer to complete than if you used a batch inserts. This could be implemented, as an example, into 100 inserts of 10,000 rows.

This reduces the overhead of the batches being inserted and, if a batch fails, it is a smaller rollback.

In any case, for SQL Server there is a bcp utility or the BULK INSERT command which could be used to do batch inserts.

And, of course, you can also implement you own code for handling this approach.

  • 2
    In general, if you need to insert a large number of rows on a table that needs an index, it's likely to be faster to drop the index, load the data and then rebuild the index. Oracle also supports a direct path bulk load option, using the /*+ APPEND */ hint.
    – BobC
    Mar 5, 2017 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.