Create index based on cluster : Create Index « Index « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Index
3. Create Index

SQL>
SQL> create cluster emp_dept_cluster ( deptno number(2) ) size 1024
  2  /


SQL> create index emp_dept_cluster_idx on cluster emp_dept_cluster
  2  /

Index created.

SQL>
SQL> create table dept
  2  ( deptno number(2) primary key,
  3    dname  varchar2(14),
  4    loc    varchar2(13)
  5  )
  6  cluster emp_dept_cluster(deptno)
  7  /

Table created.

SQL>
SQL> create table emp
  2  ( empno number primary key,
  3    ename varchar2(10),
  4    job   varchar2(9),
  5    mgr   number,
  6    hiredate date,
  7    sal   number,
  8    comm  number,
  9    deptno number(2)
 10  )
 11  cluster emp_dept_cluster(deptno)
 12  /

Table created.

SQL>
SQL> begin
  2      for x in ( select * from dept )
  3      loop
  4          insert into dept values ( x.deptno, x.dname, x.loc );
  5          insert into emp select * from emp where deptno = x.deptno;
  6      end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL>
SQL> drop cluster emp_dept_cluster;
drop cluster emp_dept_cluster
*
ERROR at line 1:
ORA-00951: cluster not empty


SQL> drop table emp;

Table dropped.

SQL> drop table dept;

Table dropped.

SQL> drop index emp_dept_cluster_idx;

Index dropped.

SQL>
SQL>