27.Indexes

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        555

6 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        555

6 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                             555

SQL> 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        555

SQL> drop index emp_idx_job_sal;

Index dropped.

To check the index is available or not on a table:

SQL> select * from all_indexes

Share this post: on Twitter on Facebook on Google+