Recently development team come up
with new plan for index rebuild maintenance activity using their job for index
rebuild instead of DBA manually running...
What their code will do?
Create the procedure on database
level - This procedure will create the dynamic index rebuild script based on
BLEVEL > 2 indexes.
Note: we are frequently rebuilding
the indexes which BLEVEL > 2 and avoid the fragmentation.
I have suggested to developer, while
index rebuild to user parallel 4 (degree value) for performance (rebuild the
indexes faster) , once they done the rebuild to reset the default degree value.
Also asked to use “REBUILD ONLINE “
option for all indexes except BITMAP INDEX. Because we can't rebuild
online for BITMAP index
After they tested in non production
region, “We can able to rebuild online for BITMAP index". I have
done index rebuild for BITMAP indexes several times. I used only REBUILD option
for BITMAP.
I am not sure... So I have tested
again.
Finally I knew, from oracle 10g
onwards, we can rebuild the BITMAP index using REBUILD ONLINE.
On Oracle 11g:
SQL> create table AM.AM_objects as select * from dba_objects;
Table created.
SQL> create bitmap index AM.AM_objects_idx on AM.AM_objects(object_id);
Index created.
SQL> alter index AM.AM_objects_idx rebuild online;
Index altered.
On Oracle 10g:
SQL> create table AM.AM_objects as select * from dba_objects;
Table created.
SQL> create bitmap index AM.AM_objects_idx on AM.AM_objects(object_id);
Index created.
SQL> alter index AM.AM_objects_idx rebuild online;
Index altered.
On Oracle 9i:
SQL> create table AM.AM_objects as select * from dba_objects;
Table created.
SQL> create bitmap index AM.AM_objects_idx on AM.AM_objects(object_id);
Index created.
SQL> alter index AM.AM_objects_idx rebuild online;
alter index AM.AM_objects_idx rebuild online
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online
No comments:
Post a Comment