Index in Oracle

Index:-
 

Index is optional structure associates with table to have direct access to the row which can be used to improve the performance.

Scenario:-

Consider above scenario, you have a book of suppose history and the book consist of 1000 pages. On the 999 page there is information about AURANGAZEB and that you want to read but you don’t know where this information resides in this book. To read information about AURANGAZEB you will scan the book page by page (full book scan). Since the information resides on 999 pages it will take much more time to find the page. But when you have an index in the book you will go to the index page and find the appropriate topic with page number and can directly jump onto that specific page. A book with an index can be referred faster than a book without an index.

 Similarly, there is an index in oracle, you have a table called EMPLOYEE (see above table) and you don’t have index on the table. You want to fetch information about the employee whose employee number is 7. In this case oracle will search the result row by row (full table scan) and once it finds the employee number 7 it will show the information (consider if your table have millions of records). But if you have index on that table then it will directly go to the index and fetch the information quickly. Table without index is still a table but it will make difference if you have an index on it.

 Syntax to create an index is:-

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME(S));

CREATE INDEX IN_EMPNO ON EMP (EMPNO);

You can create index on multiple columns:-

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_LIST);

CREATE INDEX MUL_INDX ON EMPLOYEE (EMPNO, SALARY);

Read Also:-