Do you know why index rebuild is required in oracle? If the index gets invalid or unusable state for some reason, then we have to rebuild. Also, for frequently accessed tables day by day, performance issues may occur and the index gets fragmented. That is why an index rebuild in Oracle database is required.
Today in this article, we will learn two ways to perform index rebuild in Oracle database. It can be done offline or online in two ways. There is a difference between Online and offline index rebuild.
Here are the indexes of CUSTOMERS table. we will rebuild the CUST index.
alter index <index_name> rebuild :
alter index SH.CUST_INDEX rebuild;
It means offline. If we use this, then on that table corresponding to this index, an exclusive lock will occur and DMLs on that table will get blocked during the time of index rebuild. So there may be an application partial impact means the application will not be able to access the table.
alter index <index_name> rebuild online :
alter index SH.CUST_INDEX rebuild online;
It means online If we use this clause, then DMLs may continue during the index rebuild. Here in this case a journal table will get created that keeps track of all the DMLs during this index rebuild.
Also you must check the object size before doing index rebuild. So, here are the two ways that we can perform index rebuild in Oracle database. Hope that helps!! Also you can check out the Oracle (Doc ID 232819.1) for reference.