Rebuild Index ONLINE Vs. NO-ONLINE

This last night I had to rebuild an index. Since this database is 24×7 and do it with the ONLINE option, which is used to avoid blocking while the index is rebuilt but can block and actually blocks in large systems at the end of the process, when it synchronize the changed data during the reconstruction with what is in the index. The point is that the rebuild has taken three hours. This same index in test enviroment took two hours, but if we rebuild the index without the ONLINE clause it takes only about 20 minutes.

This difference is caused by that using the “ONLINE” clause create a table that will making a tracking of all potential insert, updates, deletes that occur through the rebuild, this process is expensive, and more expensive will be while more activity has the table. Finally, Oracle have to take all these data collected in the temporary table to the index, this process produces a lock.

 

If we perform a small testcase

 

 ESLA*ANTO> REM Table have 150k rows only
ESLA*ANTO> REM rebuild index without online clause
ESLA*ANTO> alter index indice1 rebuild;

Índice modificado.

Transcurrido: 00:00:00.21
ESLA*ANTO> REM flush buffer cache and library cache for a more realistic test
ESLA*ANTO> alter system flush buffer_cache;

Sistema modificado.

Transcurrido: 00:00:00.07
ESLA*ANTO> alter system flush shared_pool;

Sistema modificado.

Transcurrido: 00:00:00.09
ESLA*ANTO> alter index indice1 rebuild online;

Índice modificado.

Transcurrido: 00:00:00.43

 

Of course, there are very few rows to get a realistic result, but pay attention to time it take.

 

Conclusions

  • Try not to use ONLINE clause if possible.
  • Whether to use ONLINE clause, look for a valley-peak period in the system.
  • In the recreation of index use options as nologging, parallel, etc..

 

HTH – Antonio NAVARRO

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s