2

I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent sessions referencing those indexes; and are there any recommendations for the same?

I have a couple of SELECT queries running in an OLAP DB on a bunch of tables. Should I stop these sessions before the rebuild? I am assuming there might be problems with locks.


UPDATE: Note that, our DBAs had advised against having active sessions during the rebuild. Also the size of the tables are in the range of 15-20GB and rebuilds can typically take 2-3hrs.

1 Answer 1

3

Index rebuild operation is DDL and thus may lock the underlying table preventing you from updating it. However queries by nature do not lock (unless FOR UPDATE clause is specified) and can read from objects even if they're locked, thus, generally, no problems will arise during the index rebuild while queries are running (maybe except for some performance degradation during rebuild).

If you want to be able to update the table having the index which is being rebuilt, you can specify ONLINE clause:

SQL> alter index table1_pk_idx rebuild online;

Otherwise the session performing DML will wait until the index is rebuilt.

For more info on index rebuild, ONLINE clause, and index rebuild restrictions, read the section "Rebuilding an Existing Index" in Administrator's Guide, and the section "ALTER INDEX" in SQL Language Reference.

2
  • UPDATE: I think you will want to schedule indexes rebuild nightly to reduce the overhead caused by rebuild or limit the resource usage of the sessions doing DML on the index base tables. Commented Jun 11, 2013 at 12:03
  • 2
    FYI, Rebuilding indexes online is not available in the Standard Edition of Oracle, but is in Enterprise. Commented Jun 11, 2013 at 18:11

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.