Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  Yasir Arsanukaev Jun 11 '13 at 12:03
    
Thank you Yasir! –  Kent Pawar Jun 11 '13 at 16:21
2  
FYI, Rebuilding indexes online is not available in the Standard Edition of Oracle, but is in Enterprise. –  Leigh Riffel Jun 11 '13 at 18:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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