Rebuild an index while it is being referenced in Oracle

indexoracle-11g-r2

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.

Best Answer

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.

Related Question