Oracle PL/SQL/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

   
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>

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.

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.

Oracle provides a clustering technique that can be very useful for an aggregation relationship.

   
SQL> --General Syntax:
SQL>
SQL> --CREATE CLUSTER <cluster schema>
SQL> --   (cluster attribute            attribute type);
SQL>
SQL> --CREATE TABLE <table schema>
SQL> --   (cluster attribute      attribute type,
SQL> --    attribute              attribute type, ....,
SQL> --    attribute              attribute type)
SQL> --   CLUSTER <cluster schema> (cluster attribute);
SQL>
SQL> --CREATE INDEX <index schema> ON CLUSTER <cluster schema>;
SQL>
SQL> --Example:
SQL>
SQL> CREATE CLUSTER Part_Cluster
  2      (hd_id            VARCHAR2(10));
Cluster created.
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>
SQL>
SQL>
SQL> CREATE INDEX Part_Cluster_Index
  2      ON CLUSTER Part_Cluster;
Index created.
SQL>
SQL>
SQL>
SQL> drop table Hard_Disk;
Table dropped.
SQL>
SQL>
SQL>
SQL> drop CLUSTER Part_Cluster ;
Cluster dropped.
SQL>