Oracle PL/SQL Tutorial/Table/Cluster

Cluster with varchar2 column

   <source lang="sql">

SQL> SQL> SQL> create cluster lookup_hash_cluster(

 2      key_col varchar2(30)
 3  )
 4  single table
 5  hashkeys 20000
 6  size 255
 7  /

Cluster created. SQL> SQL> drop cluster lookup_hash_cluster; Cluster dropped.</source>

Create cluster and set hashkeys, size

   <source lang="sql">

SQL> SQL> SQL> create cluster user_objects_cluster_hash

 2  ( username varchar2(30) )
 3  hashkeys 100
 4  size 3168
 5  /

Cluster created. SQL> SQL> drop cluster user_objects_cluster_hash; Cluster dropped. SQL> SQL></source>

Create cluster and then create table on top of it

   <source lang="sql">

SQL> create table myTable(

 2    key_col  primary key,
 3    key_val
 4  )as
 5  select object_name, max( owner||"_"||object_id )from all_objects group by object_name
 6  /

Table created. SQL> SQL> SQL> create cluster myTableHash(

 2      key_col varchar2(30)
 3  )
 4  single table
 5  hashkeys 20000
 6  size 255
 7  /

Cluster created. SQL> SQL> SQL> create table lookup_hash(

 2     key_col,
 3     key_val )
 4  cluster myTableHash(key_col)
 5  as
 6  select * from myTable;

Table created. SQL> SQL> SQL> drop table myTable; Table dropped. SQL> drop table lookup_hash; Table dropped. SQL> drop cluster myTableHash; Cluster dropped.</source>

Create index on cluster

   <source lang="sql">

SQL> create cluster user_objects_cluster_btree

 2  ( username varchar2(30) )
 3  size 1024
 4  /

Cluster created. SQL> SQL> create index user_objects_idx

 2  on cluster user_objects_cluster_btree
 3  /

Index created. SQL> SQL> drop cluster user_objects_cluster_btree; Cluster dropped. SQL></source>

drop cluster

   <source lang="sql">

SQL> create cluster user_objects_cluster_btree

 2  ( username varchar2(30) )
 3  size 1024
 4  /

Cluster created. SQL> SQL> create index user_objects_idx

 2  on cluster user_objects_cluster_btree
 3  /

Index created. SQL> SQL> drop cluster user_objects_cluster_btree; Cluster dropped. SQL></source>

Exclusive aggregation using the clustering technique

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE Hard_Disk

 2     (hd_id            VARCHAR2(10) NOT NULL,
 3      capacity         VARCHAR2(20),
 4      PRIMARY KEY (hd_id))
 5     CLUSTER Part_Cluster(hd_id);

Table created. SQL> SQL> CREATE TABLE PartType

 2     (hd_id            VARCHAR2(10) NOT NULL,
 3      PartType_id      VARCHAR2(10) NOT NULL,
 4      description      VARCHAR2(25),
 5      PRIMARY KEY (PartType_id),
 6      FOREIGN KEY (hd_id) REFERENCES Hard_Disk (hd_id))
 7     CLUSTER Part_Cluster(hd_id);

Table created. SQL> SQL> drop table Hard_Disk cascade constraints; Table dropped. SQL> drop table PartType cascade constraints; Table dropped. SQL></source>

Existence-dependent aggregation using the clustering technique

   <source lang="sql">

SQL> SQL> CREATE CLUSTER Part_Cluster

 2     (hd_id            VARCHAR2(10));

Cluster created. SQL> SQL> CREATE TABLE Hard_Disk

 2     (hd_id            VARCHAR2(10) NOT NULL,
 3      capacity         VARCHAR2(20),
 4      PRIMARY KEY (hd_id))
 5     CLUSTER Part_Cluster(hd_id);

Table created. SQL> SQL> CREATE TABLE PartType

 2     (hd_id            VARCHAR2(10) NOT NULL,
 3      PartType_id      VARCHAR2(10) NOT NULL,
 4      description      VARCHAR2(25),
 5      PRIMARY KEY (hd_id, PartType_id),
 6      FOREIGN KEY (hd_id) REFERENCES Hard_Disk (hd_id)) CLUSTER Part_Cluster(hd_id);

Table created. SQL> SQL> CREATE INDEX Part_Cluster_Index

 2  ON CLUSTER Part_Cluster;

Index created.

SQL> drop table Hard_Disk cascade constraints; Table dropped. SQL> drop table PartType cascade constraints; Table dropped.</source>