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.
- 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