-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
Indexes As our emp table is having 14 rows, it compares row by row.
It compares 1st row salary 800 > 2000 , condition is FALSE. It compares all the rows, finally rows which are satisfying the condition are retrieved. Assume if emp table is having 1 million rows, query need to perform 1 million comparisons. When there are more no of rows, query needs to perform more no of comparisons. So, to increase the performance, indexes need to be used. Types of indexes
When index is created on single column, it is simple index. Syntax: Look at this query 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. Ex: After creating the index, if you run the query Select * from emp The performance of the query is increased. When we create index, a separate structure is created with two columns.
We can visualize the index as follows
Now, when we run the query Select * from emp Instead of searching row by row in the table, searching is done on the index using algorithms
When index is created on multiple columns, it is called composite index. Look at this query Select * from emp Look at the where clause of the query. These kind of indexes which are created on multiple columns, are called as composite index. Query to see list of all the indexes Query to see the list of indexes and its corresponding table names and column names Index can also be categorized in two types.
When index column contains unique values ( no duplicates ) , it is called Unique index. Ex Step 1: Run the query to see the list of all the indexes. Select index_name , table_name , column_name Take note of the record count. Step 2: Create a table with primary key constraint Create table student ( sno number(3) PRIMARY KEY,
Step 3: Again run the query to see list of all the indexes. Select index_name , table_name , column_name Take note of the record count. So, we never create unique index manually, It is created automatically when table is create with primary key or unique constraint.
When indexed column contains duplicates, it called as Non-unique index. Ex: There may be two or more employees having same salary.
Function based index Ex Index ID3 is used when the function lower is used in where clause.
Note: Index is an object which is used to improve the performance of select stmt. |