Rebuild (RPM) of index in Oracle

After a large number of records are deleted in Oracle, the data block space used in the tables and indexes is not released.
Table move frees up the data block space occupied by the deleted records table and organizes the fragments. If you defragment the table with move, the index is invalidated, and the index needs to be rebuilt.
Rebuilding an index frees up the block space occupied by the deleted record index. Rebuilding an index not only increases the size of the index table space free space, but also improves query performance.

SQL code

1. --table move  
2. alter table tbl move;   
3.   
4. --rebuild index   
5. alter index idx_tbl_col rebuild;  
6. alter index idx_tbl_col  rebuild online;  
7.   
8. --rename   
9. alter index employee_idx  rename to employee_index_newname;  

In the ordinary case of indexing or rebuild an index, Oracle adds share locks to the base table because share locks and row-x are incompatible, that is, the base table cannot be DML during indexing.
Whether to add online, depends on your system requirements. Because rebuild blocks all DML operations when online is not added.
When we rebuild the index, Oracle directly reads the data from the original index if the online option is not added, and when we add the online option, Oracle is directly scanning the data in the table
When the index is rebuilt, the query can still use the old index. In fact, when Oracle is rebuild, the old index is not deleted during the creation of the new index until the new index rebuild successfully.
From this point you can know that rebuild is better than dropping an index and then rebuilding the index without affecting the original SQL query, but also because of this, the rebuild way to make an index requires that the free space of the corresponding tablespace is twice times the way to delete the rebuild.

You can see the size of the index space before and after rebuild with the following SQL

SQL code

1. Select segment_name, Sum (bytes)/1024/1024/1024 as gsize
2. From User_extents
3. Group by Segment_name
4. ORDER by gsize desc

Cannot directly rebuild the entire partition index
For non-composite indexes, each partition (partition) needs to be rebuild, and the entire index cannot be rebuild directly
For composite indexes, each sub-partition (subpartition) needs to be rebuild, not directly rebuild the entire index, or directly rebuild the partition
Use the following SQL to generate the corresponding Rebuild statement, and note whether to add the online
Non-composite Index:

SQL code

1. Select ' alter index ' | | | index_owner | | '. ' | | index_name | | ' rebuild Partition ' | | Partition_name | |  ' nologging parallel; '
2. From Dba_ind_partitions
3. where Index_owner = ' user_name '
4. and index_name = ' Idx_tbl_col '

Combined index:

SQL code

1. Select ' alter index ' | | | index_owner | | '. ' | | index_name | | ' rebuild Subpartition ' | | subpartition_name | |  ' parallel; '
2. From Dba_ind_subpartitions where index_owner=' &index_owner ' and index_name=' &index_name ';

Reference Rebuild Partition Index

Rebuild (RPM) of index in Oracle