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.

up vote 8 down vote accepted

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:

CREATE TABLE X_TABLE (PK INT NULL, PRIMARY KEY (PK));

INSERT INTO X_TABLE VALUES (1);
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:

DROP TABLE IF EXISTS X_TABLE_IX;

CREATE TABLE X_TABLE_IX (
ID INT NOT NULL,
VAL VARCHAR(10) NOT NULL
PRIMARY KEY (ID)
);

CREATE INDEX X_INDEX ON X_TABLE_IX (VAL);

-- insert one million rows with N from 1 to 1000000
INSERT INTO X_TABLE_IX
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.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

query 1

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

BEGIN TRANSACTION

UPDATE X_TABLE_IX
SET VAL = 'A'
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.

SELECT *
FROM X_TABLE_IX
WHERE VAL = 'A';

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. – Mark Ireland Mar 2 '17 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). – ypercubeᵀᴹ Mar 2 '17 at 16:53
  • Oracle also supports deferred constraints – BobC Mar 5 '17 at 19:07

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.

  • 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 '17 at 19:13

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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