Indexes are used for faster retrieval of queries from a table .The Index in oracle functions just like the Index in a book where we locate a topic easily by scanning the index .Oracle scans the index and locates the data location and retrieves the data quickly.
When does a table require index :
1.When we want to retrieve less than 15% of the data from a relatively large table we need to create an index on the table .
2.When a join operation is done to retrieve data from multiple tables,creating an index on the joining column will retrieve the data quicker .
3.Smaller tables do not require indexes .If a query runs longer then the size of the table has increased or there is some other problem in it that needs to be addressed.
When should we create an Index :
The ideal way to create an index is to create the table,populate the data and then create an appropriate index for the table.If we create the table with index ,each insert into the table needs an entry in the index which might take considerable amount of time .
What columns should be chosen while Indexing :
1.Values are relatively unique for the tables.
2.If the column has wide range of values it will be suitable for normal or regular index .
3.If the column has small range of values it will be suitable for bit map indexes.
4.When we use a mathematical function eg: multiplication and the column has many null values we know that only the not null value will be used for the operation.Hence creating an index on this will be helpful
5.Creating an Index which appears frequently in the where clause of the query .But if the indexed column is used in a function in the where clause do not create the Index .(Functional Index will be more useful in this case).
7.Always check the execution plan to ensure that the Index is used in the query.
8.Do not create index on a column that is frequently updated,inserted or deleted .This will add overhead since we need to do all the operations in index as well.
9.Always choose an index with high selectivity.
Selectivity for Index :
If a table has 10,000 records and the index on the table has 8000 distinct values then the selectivity of the index would be 8000/10000=0.8.The ideal selectivity is 1.That can be only achieved using a unique index on a not null column .
Composite Index :
Composite Index is a combination of one or more keys used to create an index
When we create a composite index, we must make sure that the column that is frequently used are mentioned first .
eg :
create index t_idx
on t(col1,col2,col3);
In the above index the col1,col1|col2,col1|col2|col3 are all leading portion of the index whereas
col2,col3,col2|col3 are the not leading portion of the index .
So in the above scenario only the queries that access the leading portion of the index uses the index.if we try to query the table using a where condition which has the not leading portion of the index (col2,col3,col2|col3) it will not access the index.
Choosing a key for composite index :
1.If all the columns in the where clause are used for creating the index then ordering the columns based on selectivity (higher selectivity to lower) will increase the performance.
2.If all the columns in the where clause are used for creating the index and the table is ordered on a particular key,then make sure to include that column as the first column for index.
3.If only some keys are used in the query make sure to create the index such that the frequently used column forms the leading portion of the index.
Limiting the number of indexes :
A table can have as many indexes as needed.But creating more number of indexes causes overhead especially while inserting or deleting the data.the Index also needs to be deleted or inserted which results in the overhead .Also while doing an update the corresponding index entry also needs to be updated ,Hence we must limit the use of index.
A table which is used as read only can have indexes where as a table where there are heavy DML s running it is essential to reduce the number of indexes .
Dropping the Index :
An index should be dropped under the following situation:
1.If the performance is not improved.It may be because the table is very small or because the size of the index is very small .
2.If the query we want to use is not accessing the index .If the queries we use are not accessing the index then there is no use in creating the index on the first place and hence it should be dropped .
Tablespace for Index:
An index can be created in the same tablespace or a different tablespace as of the table.
If the index is created in the same table space the backup will be easier .But with different tablespace performance will be improved since it reduces the disk contention .
But with the table and index in different tablespace a query accessing the table may not work if the index or tables tablespace is not online.
To manually prevent using Indexes :
If we want the CBO to manually prevent a query from accessing the index we can use the NO_INDEX hint or use a FULL hint which will cause a full table scan instead of the index.
When does a table require index :
1.When we want to retrieve less than 15% of the data from a relatively large table we need to create an index on the table .
2.When a join operation is done to retrieve data from multiple tables,creating an index on the joining column will retrieve the data quicker .
3.Smaller tables do not require indexes .If a query runs longer then the size of the table has increased or there is some other problem in it that needs to be addressed.
When should we create an Index :
The ideal way to create an index is to create the table,populate the data and then create an appropriate index for the table.If we create the table with index ,each insert into the table needs an entry in the index which might take considerable amount of time .
What columns should be chosen while Indexing :
1.Values are relatively unique for the tables.
2.If the column has wide range of values it will be suitable for normal or regular index .
3.If the column has small range of values it will be suitable for bit map indexes.
4.When we use a mathematical function eg: multiplication and the column has many null values we know that only the not null value will be used for the operation.Hence creating an index on this will be helpful
5.Creating an Index which appears frequently in the where clause of the query .But if the indexed column is used in a function in the where clause do not create the Index .(Functional Index will be more useful in this case).
7.Always check the execution plan to ensure that the Index is used in the query.
8.Do not create index on a column that is frequently updated,inserted or deleted .This will add overhead since we need to do all the operations in index as well.
9.Always choose an index with high selectivity.
Selectivity for Index :
If a table has 10,000 records and the index on the table has 8000 distinct values then the selectivity of the index would be 8000/10000=0.8.The ideal selectivity is 1.That can be only achieved using a unique index on a not null column .
Composite Index :
Composite Index is a combination of one or more keys used to create an index
When we create a composite index, we must make sure that the column that is frequently used are mentioned first .
eg :
create index t_idx
on t(col1,col2,col3);
In the above index the col1,col1|col2,col1|col2|col3 are all leading portion of the index whereas
col2,col3,col2|col3 are the not leading portion of the index .
So in the above scenario only the queries that access the leading portion of the index uses the index.if we try to query the table using a where condition which has the not leading portion of the index (col2,col3,col2|col3) it will not access the index.
Choosing a key for composite index :
1.If all the columns in the where clause are used for creating the index then ordering the columns based on selectivity (higher selectivity to lower) will increase the performance.
2.If all the columns in the where clause are used for creating the index and the table is ordered on a particular key,then make sure to include that column as the first column for index.
3.If only some keys are used in the query make sure to create the index such that the frequently used column forms the leading portion of the index.
Limiting the number of indexes :
A table can have as many indexes as needed.But creating more number of indexes causes overhead especially while inserting or deleting the data.the Index also needs to be deleted or inserted which results in the overhead .Also while doing an update the corresponding index entry also needs to be updated ,Hence we must limit the use of index.
A table which is used as read only can have indexes where as a table where there are heavy DML s running it is essential to reduce the number of indexes .
Dropping the Index :
An index should be dropped under the following situation:
1.If the performance is not improved.It may be because the table is very small or because the size of the index is very small .
2.If the query we want to use is not accessing the index .If the queries we use are not accessing the index then there is no use in creating the index on the first place and hence it should be dropped .
Tablespace for Index:
An index can be created in the same tablespace or a different tablespace as of the table.
If the index is created in the same table space the backup will be easier .But with different tablespace performance will be improved since it reduces the disk contention .
But with the table and index in different tablespace a query accessing the table may not work if the index or tables tablespace is not online.
To manually prevent using Indexes :
If we want the CBO to manually prevent a query from accessing the index we can use the NO_INDEX hint or use a FULL hint which will cause a full table scan instead of the index.
