Cluster Tables

 

Cluster Tables

一、         Cluster table创建语法

 

1、Creating Clusters

CREATE CLUSTER emp_dept (deptno NUMBER(3))

   SIZE 600

   TABLESPACE users

   STORAGE (INITIAL 200K

      NEXT 300K

      MINEXTENTS 2

      PCTINCREASE 33);

2、Creating Clustered Tables

CREATE TABLE dept (

   deptno NUMBER(3) PRIMARY KEY, . . . )

   CLUSTER emp_dept (deptno);

3、Creating Cluster Indexes

CREATE INDEX emp_dept_index

   ON CLUSTER emp_dept

   TABLESPACE users

   STORAGE (INITIAL 50K

      NEXT 50K

      MINEXTENTS 2

      MAXEXTENTS 10

      PCTINCREASE 33);

 

二、实验分析

 

1、创建cluster,index，cluster table

sys@TEST> create cluster cluster_doudou

  2  (deptid number(2))

  3  size 1024

  4  /

Cluster created.

sys@TEST> create index cluster_doudou_index on cluster cluster_doudou;

Index created.

sys@TEST> create table cluster_01

  2  (deptid number(2) primary key,

  3  name varchar2(2) ) cluster cluster_doudou(deptid);

Table created.

 

2、分析cluster table使用条件（权限暂不考虑）

创建cluster table定义中，不使用cluster中的列（deptid），创建失败

sys@TEST> create table cluster_02 (name varchar2(2),dd number(2)) cluster cluster_doudou(deptid);

create table cluster_02 (name varchar2(2),dd number(2)) cluster cluster_doudou(deptid)

*

ERROR at line 1:

ORA-00945: specified clustered column does not exist

sys@TEST> create table cluster_02

  2  (deptid number(2), name varchar2(2) ) cluster cluster_doudou(deptid);

Table created.

 

创建cluster table定义中，使用deptid，但是定义的类型不同，创建失败

sys@TEST>  create table cluster_03 (deptid varchar2(20) , name varchar2(2) ) cluster cluster_doudou(deptid);

 create table cluster_03 (deptid varchar2(20) , name varchar2(2) ) cluster cluster_doudou(deptid)                                                                                      *

ERROR at line 1:

ORA-01753: column definition incompatible with clustered column definition

sys@TEST> create table cluster_03

  2  (name varchar2(20),deptid number(2)) cluster cluster_doudou(deptid);

Table created.

 

三、         总结

 

1、create cluster table的时候,语法中必须包含cluster中的列deptid

2、cluster中列deptid定义类型（deptid NUMBER(2)）必须相同