Index, Clusters and Roles in Oracle

 Index : It is a pointer locates the physical address of data
 It will improve performance of oracle while  retrieving or  manipulating data from Table.
 It is automatically activated when indexed column  is  used in " Where " clause.
 It is stored in "User_indexes" system Table.

 Normal Index: create index idx1 on emp(job);
 select * from emp where job = 'CLERK';

 Composite Index: create index idx2 on student(course,timing);
 select * from student where course = 'oracle'
 and timing = '11.30 am' ;

 Unique Index: create unique index idx3 on dept(dname);
 select * from dept where dname = 'SALES';
 insert into dept values(50,'SALES','Chennai');

 Function based index: ( DBA ) create index idx4 on emp(upper(job));
 select * from emp where upper(job) = 'MANAGER';

 desc user_indexes select * from user_indexes;
 drop index idx1;  -- Removing Index
---------------------------------------------------------------
 

 Clusters : It holds the common column shared by 2 tables.
 It will improve the performance while retrieving or    manipulating data from Master - Detail tables.
 It is stored in user_clusters system table.
 It has to be defined before creating tables.
 It cannot be applied to existing tables.

 1. create cluster c1(deptno number(2));

 2. create table dept(deptno number(2) primary             key, dname varchar2(20),loc varchar2(20))       cluster c1(deptno);

 3. create table emp(empno number(4)
     primary key,  ........................
     ........................
    deptno number(2) references dept)
    cluster c1(deptno);

 4. create index cidx on cluster c1;

  desc user_clusters
  select * from user_clusters;

  drop cluster c1;
 drop cluster c1 including tables;
---------------------------------------------------------------
 

 Roles : Used to share mutiple objects with other users  easily .
 Defined by "DBA" only .
 It holds the collection of permissions to be shared .
 It is stored in User_roles system table .

 create role hr;
 grant all on emp to hr;
 grant all on dept to hr;
 grant insert,select on incr to hr;
 grant hr to user1,user2;
 revoke delete on dept from hr;
 grant insert on salgrade to hr;
 grant hr to user3;

 desc user_roles
 select * from user_roles;
 drop role hr;
--------------------------------------------------------------------