Function based Index :
Functional Based Indexes are most beneficial if the where clause of the SQL statement contains a function.
Eg :
select * from employees
where upper(employee_name)='ABC;
If the above statement is used several times then creating an index on the employee_name alone will not use the index.Hence we must create an index on upper(employee_name).This would speed the query execution since the index will be created on the upper column .
Create index v_idx on employees(upper(employee_name));
To enable this index we must set two session parameters.
Query_rewrite_enabled :
This session parameter has three values .False,True,Force .If the session parameter is set to false it will not use the functional index for computing the values for the functional based index.If set to force will ensure the query is re written using the index.
Bitmap Index :
Bitmap index is mainly used in the data warehousing environment where the DMLs are less.Bitmap indexes are very useful for low cardinality columns. ie when the cardinality is less than 0.1%.
For Eg :Creating a bitmap index on the Gender or the Marital Status column has very less distinct values.Hence the Bitmap Indexes are very useful here .The Bitmap Index stores the rowid along with the bit if set means it contains a key value.Hence scanning the index and retreiving the data is easy.
Disadvantage :
The disadvantage of bitmap index is that if the table is manipulated often using inserts or updates,it will cause an overhead for the index.
Also deadlock condition may arise if multiple sessions try to insert the record into the table at the same time.
Btree index:
The Btree index is organized in the form of a tree and hence the name.This index is very useful if we have wide range of distinct values .It starts with a root node and the leaf nodes .Once when a query is issued it goes to the root node and decides on which leaf nodes the data exists and then traverse the leaf node to locate the data .
Functional Based Indexes are most beneficial if the where clause of the SQL statement contains a function.
Eg :
select * from employees
where upper(employee_name)='ABC;
If the above statement is used several times then creating an index on the employee_name alone will not use the index.Hence we must create an index on upper(employee_name).This would speed the query execution since the index will be created on the upper column .
Create index v_idx on employees(upper(employee_name));
To enable this index we must set two session parameters.
Query_rewrite_enabled :
This session parameter has three values .False,True,Force .If the session parameter is set to false it will not use the functional index for computing the values for the functional based index.If set to force will ensure the query is re written using the index.
Bitmap Index :
Bitmap index is mainly used in the data warehousing environment where the DMLs are less.Bitmap indexes are very useful for low cardinality columns. ie when the cardinality is less than 0.1%.
For Eg :Creating a bitmap index on the Gender or the Marital Status column has very less distinct values.Hence the Bitmap Indexes are very useful here .The Bitmap Index stores the rowid along with the bit if set means it contains a key value.Hence scanning the index and retreiving the data is easy.
Disadvantage :
The disadvantage of bitmap index is that if the table is manipulated often using inserts or updates,it will cause an overhead for the index.
Also deadlock condition may arise if multiple sessions try to insert the record into the table at the same time.
Btree index:
The Btree index is organized in the form of a tree and hence the name.This index is very useful if we have wide range of distinct values .It starts with a root node and the leaf nodes .Once when a query is issued it goes to the root node and decides on which leaf nodes the data exists and then traverse the leaf node to locate the data .
