NON UNIQUE INDEXES

Non unique index is a part of indexes that does not impose any restrictions on column values.

It mean that it allow duplicate values.

Syntax:-

SQL> create index  on  ();  
 
Example:-
Create index na_non_job on EMP (job);
 
 
--index created
 
So when ever we use this index column (JOB) in “where” clause, index (na_non_job) will automatically activated.
 
Example:-
SQL>Select *from EMP where job=’CLERK’;


 
SQL>insert into EMP (empno, ename, job) values (7682,’SCOTT’,’CLERK’);
 
 
-- 1 row inserted
 
 
 
 
For dropping index
Syntax:-
SQL>Drop index ;
 
 
 
Example:-
SQL>Drop index idx1;
 
-- Index dropped