в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:54:55Z

1 версия

Новая страница

==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==



<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>

==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>

==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>

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



<source lang="sql">

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>

</source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Table/Cluster - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия