INDEX_REBUILDING

INDEX_REBUILDING

·         Index plays crucial role in terms of performance of databases.

·         Index rebuilding is a mandatory activity in any oracle database environment.

·         Because oracle indexes are inadequate in its nature means they are not auto rebalanced whenever there is more number of inserts and deletes happens the free chunks will remain same

·         This leads to performance degradation in the real time

·         So all we need to do is rebuid them

·         There are certain conditions to rebuild the indexes as per the oracle standards

One is Height of the index

And the other is ration between leaf rows and deleted leaf rows

To find this all we need to do is analyze the structure of the index

Syntax to analyze:

SQL> ANALYZE INDEX INDEX_NAME VALIDATE STRUCTURE;

After validating the structure execute the below query to find the height and ratio

Syntax:

SELECT NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS, (DEL_LF_ROWS/LF_ROWS)*100 AS RATIO FROM INDEX_STATS;

Well the conditions to rebuild them are

If Height > 3 and the Ratio >=20%

So now to rebuild them we have two options using online and without using online

What happens if we specify rebuild online ….?

You have the option of rebuilding the index online. Rebuilding online enables you to update base tables at the same time that you are rebuilding. The following statement rebuilds the index online:

Syntax:

ALTER INDEX INDEX_NAME REBUID;

ALTER INDEX INDEX_NAME REBUILD ONLINE;