Database Tables - Expert Oracle Database Architecture - page 386

key. It is a primary key, in effect, where each cluster key value points to a single block in the cluster itself. So, when we
ask for the data in department 10 , Oracle will read the cluster key, determine the block address for that, and then read
the data. The cluster key index is created as follows:
EODA@ORA12CR1> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster
3 /
Index created.
It can have all of the normal storage parameters of an index and can be stored in another tablespace. It is just a
regular index, so it can be on multiple columns; it just happens to index into a cluster and can also include an entry
for a completely null value (see Chapter 11 for the reason why this is interesting). Note that we do not specify a list
of columns in this CREATE INDEX statement—that is derived from the CLUSTER definition itself. Now we are ready to
create our tables in the cluster:
EODA@ORA12CR1> 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.
EODA@ORA12CR1> 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) references dept(deptno)
10 )
11 cluster emp_dept_cluster(deptno)
12 /
Table created.
Here, the only difference from a normal table is that we used the CLUSTER keyword and told Oracle which column
of the base table will map to the cluster key in the cluster itself. Remember, the cluster is the segment here, therefore
this table will never have segment attributes such as TABLESPACE , PCTFREE , and so on—they are attributes of the
cluster segment, not the table we just created. We can now load them up with the initial set of data:
EODA@ORA12CR1> insert into dept
2 ( deptno, dname, loc )
3 select deptno+r, dname, loc
4 from scott.dept,
5 (select level r from dual connect by level < 10);
36 rows created.