Oracle index is an optional database object generally created to improve the performance of select statement.
Index is space demanding object. Index improves the performance of select statement and at the same time it is also going to degrade the performance of DML operations.
Note : Indexes are generally created on the columns which are most frequently used in the where clause of a select statement.
The traditional index of oracle is B-Tree index(Balanced/Binary Tree).
B-Tree index minimizes the search criteria by almost 50%.
Optimizer is an intelligent component which determines the execution plan of a select statement.
Optimizer considers index if we are selecting less than 10% of data(8% to 10%).
Note : Create a reverse key index on columns which has sequence of integer values.
Create "function based index" if we are using functions in the where clause.
SYS> select * from emp where upper(ename)='SCOTT';
SYS> create index emp_index_ename on emp upper(ename);
We can minimize the index creation time by specifying 'nologging' or by enabling 'parallel degree'.
Create a separate tablespace for all indexes like there are separate pages for index in the book.
Create bitmap indexes on 'low cardinality columns'.
A column with repeated values is called a low cardinality column.
e.g : deptno in emp, region column, gender column...
SYS> create bitmap index emp_bitmap_deptno on emp(deptno);
In 11g, oracle has introduced invisible indexes, these indexes are not visible to the optimizer.
Optimizer uses invisible indexes if we set a parameter "optimizer_use_invisible_indexes=true" and we also need to pass an hint in the select statement(since third party vendors will not allow to create index).
SYS> create index emp_index_sal on emp(sal) invisible;
SYS> select /*+ index(emp,emp_index_sal) */ * from wmp where sal=5000;
SYS> select * from customer_details where first_name='SCOTT' and last_name='TIGER';
We can create an index upto 32 columns.
SYS> create index customer_index_fname on customer_details(first_name);
SYS> create index customer_index_lname on customer_details(last_name);
SYS> create index customer_index_lname_fname on customer_details(last_name,first_name);
If we create an index on more than one column such type of index is called composite/concatinated index.
If we are using more than 1 columns in the where clause, recommanded to create concatinated index rather than individual index.
SYS>create index customer_index_lname_fname on customer_details(fname,lname);
SYS>select * from customer_details where fname='SCOTT' and lname='KING';
SYS>select * from customer_details where lname='KING';
SYS>select * from customer_details where fname='SCOTT';
In older versions of oracle, optimizer considers concatinated index if we use the leading edge column in the where clause otherwise it ignores.
Note : In modern versions of oracle, optimizer considers concatinated index even if we use non leading edge column, such feature is called "skip scan feature(skip scan index)".
If a table is having huge volume of data, we can compress the index at the time of creation to minimise the space usage.
SYS>create index emp_index_fname_lname on emp(fname,lname) compress 2;
Whenever there is a gragmentation in the index or if the B-level of the index is greater than 3, recommanded to rebuild the index.
SYS>alter index emp_index_fname_lname rebuild online;
SYS>alter index<indexname> shrink space;
SYS>spool indexrebuild.sql
SYS>select 'alter index'||index_name||'rebuild online;' from dba_indexes where owner ='SCOTT';
In case of Index Organisation Table(IOT) the table itself will be organised in the form of index.
In order to create a IOT, table must and should have primary key column.
SYS>create table emp
empno number primary key,
ename varchar(20),
sal number(5,2) organization index;
