Indexes are used to improve the performance of select statements.
Look at this query
Select * from emp
Where sal > 2000;
As our emp table is having 14 rows, it compares row by row.
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7369
SMITH
CLERK
7902
17-Dec-80
800
20
7499
ALLEN
SALESMAN
7698
20-Feb-81
1600
300
30
7521
WARD
SALESMAN
7698
22-Feb-81
1250
500
30
7566
JONES
MANAGER
7839
2-Apr-81
2975
20
7654
MARTIN
SALESMAN
7698
28-Sep-81
1250
1400
30
7698
BLAKE
MANAGER
7839
1-May-81
2850
30
7782
CLARK
MANAGER
7839
9-Jun-81
2450
10
7788
SCOTT
ANALYST
7566
9-Dec-82
3000
20
7839
KING
PRESIDENT
17-Nov-81
5000
10
7844
TURNER
SALESMAN
7698
8-Sep-81
1500
0
30
7876
ADAMS
CLERK
7788
12-Jan-83
1100
20
7900
JAMES
CLERK
7698
3-Dec-81
950
30
7902
FORD
ANALYST
7566
3-Dec-81
3000
20
7934
MILLER
CLERK
7782
23-Jan-82
1300
10
It compares 1st row salary 800 > 2000 , condition is FALSE.
It compares 2nd row salary 1600 > 2000, condition is FALSE.
It compares 3rd row salary 1250 > 2000, condition is FALSE.
It compares 4th row salary 2975 > 2000, condition is TRUE.
It compares all the rows, finally rows which are satisfying the condition are retrieved.
The point it the no of comparisons.
As the emp table is having 14 rows, it performs 14 comparisions.
Assume if emp table is having 1 million rows, query need to perform 1 million comparisons.
Performing 1 million comparisons takes long time, thus query performance is decreased.
When there are more no of rows, query needs to perform more no of comparisons.
When there are more no of comparisons, the performance is decreased.
So, to increase the performance, indexes need to be used.
Types of indexes
Simple Index
Composite index
Simple Index
When index is created on single column, it is simple index.
Syntax:
Create index < index_name> on table_name ( col_name);
Look at this query
Select * from emp
Where sal > 2000;
Assume emp table is having 1 million records, the query performance is slow.
Look at the where clause of the query, sal column is used.
Index should be created on columns which are used in where clause.
To improve the performance, we need to create index on sal column.
Ex:
Create index ID1 in emp (sal);
After creating the index, if you run the query
Select * from emp
Where sal > 2000;
The performance of the query is increased.
When we create index, a separate structure is created with two columns.
First column is the ROWID.
Second column is the column on which index is created.
Rows in the index are arranged in ascending order of the indexed column.
We can visualize the index as follows
ROWID
SAL
AABqJuAB+AAA2O4AAA
800
AABqJuAB+AAA2O4AAL
950
AABqJuAB+AAA2O4AAK
1100
AABqJuAB+AAA2O4AAC
1250
AABqJuAB+AAA2O4AAE
1250
AABqJuAB+AAA2O4AAN
1300
AABqJuAB+AAA2O4AAJ
1500
AABqJuAB+AAA2O4AAB
1600
AABqJuAB+AAA2O4AAG
2450
AABqJuAB+AAA2O4AAF
2850
AABqJuAB+AAA2O4AAD
2975
AABqJuAB+AAA2O4AAH
3000
AABqJuAB+AAA2O4AAM
3000
AABqJuAB+AAA2O4AAI
5000
Now, when we run the query
Select * from emp
Where sal > 2000;
Instead of searching row by row in the table, searching is done on the index using algorithms
Gets the bulk of ROWID, using which rows are displayed.
Composite index
When index is created on multiple columns, it is called composite index.
Look at this query
Select * from emp
Where sal > 2000 and job = MANAGER;
Look at the where clause of the query.
Sal and job columns are used.
We know, index should be created on columns which are used in WHERE clause.
As WHERE clause contains SAL and JOB columns, we need to create index on two columns.
Ex:
Create Index ID2 on emp ( sal, job );
These kind of indexes which are created on multiple columns, are called as composite index.
Query to see list of all the indexes
Select index_name , table_name
From user_indexes;
Query to see the list of indexes and its corresponding table names and column names
Select index_name , table_name , column_name
From user_indexes;
Index can also be categorized in two types.
Unique Index
Non-Unique index
Unique Index
When index column contains unique values ( no duplicates ) , it is called Unique index.
Unique index is created automatically, when we create a table with primary key constraint or unique constraint.
Ex
Step 1: Run the query to see the list of all the indexes.
Select index_name , table_name , column_name
From user_indexes;
Take note of the record count.
Step 2: Create a table with primary key constraint
Step 3: Again run the query to see list of all the indexes.
Select index_name , table_name , column_name
From user_indexes;
Take note of the record count.
Record count in step 3 is one more than record count in step 2.
So, we never create unique index manually, It is created automatically when table is create with primary key or unique constraint.
Non-unique index
When indexed column contains duplicates, it called as Non-unique index.
Ex:
Create index ID1 in emp (sal);
There may be two or more employees having same salary.
So the indexed column sal can contain duplicates, it is called Non-unique index.
Index ID1 is an example of simple index as well as Non-unique index.
Function based index
When index is created using functions, it is called function based index.
Ex
Create index ID3 on emp ( lower(ename) );
Index ID3 is used when the function lower is used in where clause.
I.e. select * from emp
Where lower (ename)=king;
Note: Index is an object which is used to improve the performance of select stmt.