Rebuild Index

Fragmentation or Rebuilding of Index:

Scenario:

Let us consider one Table Emp, which is having 1000 rows and created index on the columns. The User was deleted 500 rows from the Emp Table.

But the deleted rows will be still maintained due to High Water Mark. This may occur in issue of Database performance.

While Re-Building the index, the index Re-creates the index only for existing rows in the table. This Re-Building the index is called the Index Fragmenation.

Steps to Fragmention:

Step 1: Let us consider, TEST is the index name for the Emp table.

Analyze Index TEST validate structure;
--------> Index is Analyzed.

Step 2: Select * from Index_status;

Step 3: Select Del_lf_rows, lf_rows from index_stats;

Del_lf_rows--> No.of deleted rows
lf_rows--> Total no.of rows

Step 4: Select (del_lf_rows/lf_rows*100)from index_stats where name='TEST';

Step 5: The result is 30%, so we must rebuild the index is called fragmentation.

Note: The result is >30% means, we must rebuild/fragment the particular index
The result is <30% means, we must coalesce/defragment the particular index

Step 6: How to Fragment/Rebuild the index

Alter Index TEST Rebuild;

Step 7: How to Coalesce/De fragment the index

Alter Index TEST Coalesce;

Step 8: After the rebuilding/or Coalescing the index we must check out the status of the index;

Analyze Index TEST validate structure;
--------> Index is Analyzed.

Step 9: Select (del_lf_rows/lf_rows*100) from index_stats where name='IBM';

Step 10: The above calculation result should be zero(0).