INDEX : Index is an object which is used to improve the performance of the select query
Syntax: SQL> create index index_name on table (col1,col2…);
SQL> select * from emp_new;
ENO ENAME SAL JOB DOJ DEPT_ID DAY 115 CHANDU 25000 manager 10-MAR-12 222
105 kumar 12200 555
111 RAM 12000 A 12-DEC-14 222
112 RAMESH 25000 B 29-DEC-14 333
113 KRISHNA 30000 C 03-JAN-14 555
114 SHEKAR 56000 manager 01-JAN-14 5556 rows selected.
SQL> create index emp_idx_sal on emp_new(sal);
Index created.
SQL> select * from emp_new;
ENO ENAME SAL JOB DOJ DEPT_ID DAY 115 CHANDU 25000 manager 10-MAR-12 222
105 kumar 12200 555
111 RAM 12000 A 12-DEC-14 222
112 RAMESH 25000 B 29-DEC-14 333
113 KRISHNA 30000 C 03-JAN-14 555
114 SHEKAR 56000 manager 01-JAN-14 5556 rows selected.
SQL> select * from emp_new where sal between 10000 and 15000;
ENO ENAME SAL JOB DOJ DEPT_ID DAY 111 RAM 12000 A 12-DEC-14 222
105 kumar 12200 555SQL> create index emp_idx_job_sal on emp_new(job,sal);
Index created.
SQL> select * from emp where job in (‘manager’) and sal between 25000 and 60000;
ENO ENAME SAL JOB DOJ DEPT_ID DAY 115 CHANDU 25000 manager 10-MAR-12 222
114 SHEKAR 56000 manager 01-JAN-14 555SQL> drop index emp_idx_job_sal;
Index dropped.
To check the index is available or not on a table:
SQL> select * from all_indexes
