Index Maintenance and Index Operations in Oracle Database

I will explain Index Maintenance and Index Operations in Oracle Database in this article.

 

Index Maintenance and Index Operations in Oracle

 

If you don’t know What is the Index and how to create it, read following article.

 

Since indexes are an important object for us, we need to perform index maintenance at certain intervals in order to ensure good performance in production systems. The operations we perform on the indexes are generally as follows.

 

• Rebuild Index
• Analyze Index
• Invisible Index
• Rename Index
• Drop Index

 

Index Rebuild Oracle

Indexes in our tables may become corrupted or fragmented as a result of intensive DML operations (Insert, Delete, etc.) of the tables. Indexes are broken and UNUSABLE when they are moved to new tablespaces outside of DML operations. Index should rebuild in this cases. Corrupted, Unusable or broken Indexes are Rebuilded as follows.

SQL> alter index <index_name> rebuild;
SQL> alter index MID_IX rebuild;

You can rebuild indexes Online to prevent Lock and downtime.

SQL> ALTER INDEX <index name> REBUILD ONLINE;
SQL> ALTER INDEX MID_IX REBUILD ONLINE;

Rebuild Indexes in parallel that are widely used in production environment will speed up your job and finish in less time. It is recommended that you do not give more parallel than your CPU count when using Parallel. Indexes can be rebuilt in parallel and online as follows.

SQL> ALTER INDEX <index name> REBUILD ONLINE PARALLEL <parallel_count>;
SQL> ALTER INDEX MID_IX REBUILD ONLINE PARALLEL 4;

 

 

Now let’s ask the most important question. How do we understand if an index should be rebuilt ?It is very important to determine whether the indexes need Rebuild, so that index maintenance should be performed.

You can decide whether our tables should be indexed by querying the SYS.INDEX_STATS table. When we first query this table, it will be empty as below because the statistics for our index have not been collected.

SQL> select * from index_stats;
no rows selected
SQL>

Analyze index command and Validate Structure command should be executed to collect statistics of index. The statistics of the Index are collected as follows.

SQL> ANALYZE INDEX <index_name> VALIDATE STRUCTURE;
SQL> ANALYZE INDEX MID_IX VALIDATE STRUCTURE;

There will always be a single record in the index_stats table. Ex; The Analyze command is run as follows to look at the statistics for the EMP_NAME_IX index in the Employee table of the HR Schema.

SQL> ANALYZE INDEX HR.EMP_NAME_IX VALIDATE STRUCTURE;
Index analyzed.
SQL>

When you run the index_stats command, some values like the following will generate.

SQL> select * from index_stats;

 

 

1. When the HEIGHT value in the index_stats table is greater than 4, Index should rebuild.
2. When the DEL_LF_ROWS / LF_ROWS ratio is greater than 20%, Index should rebuild.

 

 

Invisible Indexes Oracle

Indexes can now be made invisible with Oracle 11g. Making the indexes invisible means that they are not visible by the Query Optimizer, meaning that the Index is physically exists but invisible while the queries are running, so that the Optimizer will not see the Index. This is especially useful when testing the performance of Indexes on tables. Before Oracle 11g, we were trying to do this by leaving the Indexes Unusable.

 

 

We can change an index to Invisible as follows.

 

SQL> Alter index MID_IX invisible;

 

You can make an index visible from invisible like following.

SQL> Alter index MID_IX visible;

 

Rename Index Oracle

When needed, you can rename the names of the indexes as follows. The Index should be rebuild immediately after rename operation.

SQL> ALTER INDEX <orjinal_index> RENAME TO <indexin_yeni_adı>;
SQL> alter index MID_IX rename to MUSID_IX;

Drop Index Oracle

if Indexes are unnecessary or not used, they should be deleted because they consume system resources in vain. Indexes can be deleted as follows.

SQL> DROP INDEX <index_name>;
SQL> drop index MID_IX;

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.