Oracle PL/SQL Tutorial/Table/Cluster

Cluster with varchar2 column

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.

Create cluster and set hashkeys, size

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>

Create cluster and then create table on top of it

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.

Create index on cluster

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>

drop cluster

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>

Exclusive aggregation using the clustering technique

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>

Existence-dependent aggregation using the clustering technique

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.