Rebuilding Indexes in Oracle

•   First we will discuss what are indexes?

An indexes are schema objects that contains an entry for each value that appears in the indexed columns of the table and provides fast access to rows.

 We can say Indexes are the easiest way to improve the performance of long running queries with full tables scan. By properly indexing large tables query completion time
Can go from hours to minutes.

Indexes not only improve Select queries but also it improves Update & Delete

•  Some Indexes Names with their Functions:-

B-Tree index :- This index is default index in oracle, Good for high cardinality (not identical or unique values like usernames, user-ids)

Bitmap index:- Excellent in Data warehouse environments Good for low cardinality values ( Less repeated values ex: Gender)

Reverse index:-  A form of B-tree index; useful to balance I/O in an index that has many sequential inserts.

Function Based index:- Good for columns that have SQL functions applied to them. This can be used with either a B-tree or bitmap index.
           
Concatenate or Composite index: - Is an index on multiple columns in a table.

Global Partitioned Index:- Global index across all partitions in a partitioned table or regular table. This can be a B-tree index type and can’t be a bitmap index type.

Local Partitioned Index:- Local index based on individual partitions in a partitioned table. This can be either a B-tree or bitmap index type.

Descending Index:- A form of B-tree index; used with indexes where corresponding column values are sorted in a descending order (the default order is ascending).

INDEX STATISTICS :-

 Height      : This is the height of the index which refers to the number of levels that are                       spawned by the index as a result in row inserts.
                    When a large number of rows are added to a table,                                                           Oracle may spawn additional levels of an index to accommodate the new                         rows.Hence, an  Oracle index may have four levels, but only in those                               areas of the index tree where the massive inserts have occurred.
                      Oracle indexes can support many millions of entries in three levels, and                         any  Oracle index that has four or more levels  would benefit from                                    rebuilding.

LF_ROWS     : Number of leaf rows (values in the index) These leaf-rows will grow as                            rows inserts in the table.

LF_BLKS     :  Number of leaf blocks in the index.


DEL_LF_ROWS :  The number of index nodes that have been logically deleted as a                                      result of row deletes. Remember that Oracle leaves "dead" index                                    nodes in the index when rows are deleted.
                               This is done to speed up SQL deletes.Oracle does not re-balance                                   the index tree when rows are deleted.

•   Creating Index is very simple.

CREATE INDEX emp_idx ON emp(ename)TABLESPACE users;

•  When to rebuild the indexes.

                                              
When there is lot of  DML happened indexes becomes fragmented due to this indexes takes more time to give the output and index height will also increase.

                 OR

 Another rebuild condition would be cases where deleted leaf nodes comprise more than 20 percent of the index nodes.

• Steps For Rebuilding Index:-

You have to collect some information to rebuild the indexes.

Query the table in which indexes are created.

You can see here multiple indexes are created on multiple columns.

SQL> select table_name,index_name,column_name from user_ind_columns where table_name='SHSHTDTA'

TABLE_NAME       INDEX_NAME         COLUMN_NAME
---------- --------------- -----------------------------------------------------------
SHSHTDTA               ASDCAD                       SDSHID
SHSHTDTA               ASDCAD                       SDMEDN
SHSHTDTA               IX_PANIC                     SDTYPE
SHSHTDTA               IX_PANIC                     SDHSCD
SHSHTDTA               IX_SHDATE                  SDORDN
SHSHTDTA               IX_SHDATE                  SDMDDT
SHSHTDTA               SHSHTDTA_INDX        SDITEM
SHSHTDTA               SHSHTDTA_INDX        SDOCUR
SHSHTDTA               SHSHTDTA_INDX        SDMDDT

>> Index Analyzing:  When you analyze the index oracle will pool the latest index statistics In index_stats tables.

SQL> analyze index HMIS.IX_PANIC validate structure;

Index analyzed.


SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;

NAME                               HEIGHT    LF_ROWS     LF_BLKS       DEL_LF_ROWS
------------------------------ ---------- ---------- ---------- -----------------------------------------
IX_PANIC                               4         2050473            14218                 53342

• This is the Good candidate key for rebuilding.

SQL> alter index HMIS.IX_PANIC rebuild online.

Index altered.

SQL> select name,height,lf_rows,lf_blks,del_lf_rows from index_stats;

NAME                               HEIGHT      LF_ROWS     LF_BLKS        DEL_LF_ROWS
------------------------------ ---------- ---------- -------------------------------------------------------
IX_PANIC                                3           2050473             4221                      0

Happy Learning…..