Oracle PL/SQL Tutorial/Table/Cluster

Материал из SQL эксперт

Перейти к: навигация, поиск

Содержание

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.