- 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…..
