SQL> select cluster_name,tablespace_name from user_clusters;

CLUSTER_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
CLUSTER1                       TBS_HANG

---查詢cluster table資訊
SQL> desc user_clu_columns;
Name            Type           Nullable Default Comments
--------------- -------------- -------- ------- -----------------------------------------------------
CLUSTER_NAME    VARCHAR2(30)                    Cluster name
CLU_COLUMN_NAME VARCHAR2(30)                    Key column in the cluster
TABLE_NAME      VARCHAR2(30)                    Clustered table name
TAB_COLUMN_NAME VARCHAR2(4000) Y                Key column or attribute of object column in the table

SQL> select cluster_name,clu_column_name,table_name,tab_column_name from user_clu_columns;

CLUSTER_NAME                   CLU_COLUMN_NAME                TABLE_NAME                     TAB_COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ --------------------------------------------------------------------------------
CLUSTER1                       A                              T_TEST                         A
CLUSTER1                       A                              T_TEST_LOB                     A

---查詢cluster hash表示式資訊
SQL> desc user_cluster_hash_expressions;
Name            Type         Nullable Default Comments
--------------- ------------ -------- ------- --------------------------------
OWNER           VARCHAR2(30)                  Name of owner of cluster
CLUSTER_NAME    VARCHAR2(30)                  Name of cluster
HASH_EXPRESSION LONG         Y                Text of hash function of cluster

----cluster鍵列的操作限制
Restrictions on Cluster Data Types Cluster data types are subject to the following restrictions:

-----資料型別不能是long,blob,clob等
You cannot specify a cluster key column of data type LONG, LONG RAW, REF, nested table, varray, BLOB, CLOB, BFILE, the Any* Oracle-supplied types,
or user-defined object type.

--可以指定資料型別為rowid,但oracle不保證其列上的值為合理的rowid
You can specify a column of type ROWID, but Oracle Database does not guarantee that the values in such columns are valid rowids.

---如刪除cluster須先刪除其屬表
SQL> drop cluster cluster1;

drop cluster cluster1

ORA-00951: cluster not empty

SQL> drop table t_test purge;

Table dropped

SQL> drop table t_test_lob purge;

Table dropped

SQL> drop cluster cluster1;

Cluster dropped

----測試hash cluster
SQL> create cluster cluster1(a int) hashkeys 10;

Cluster created

SQL> desc user_cluster_hash_expressions;
Name            Type         Nullable Default Comments
--------------- ------------ -------- ------- --------------------------------
OWNER           VARCHAR2(30)                  Name of owner of cluster
CLUSTER_NAME    VARCHAR2(30)                  Name of cluster
HASH_EXPRESSION LONG         Y                Text of hash function of cluster

SQL> select owner,cluster_name,hash_expression from user_cluster_hash_expressions;

OWNER                          CLUSTER_NAME                   HASH_EXPRESSION
------------------------------ ------------------------------ --------------------------------------------------------------------------------

SQL> drop cluster cluster1;

Cluster dropped
---如下指定hash keys
--hash keys語義

HASHKEYS Clause
----hashkeys指定hash cluster有多少個hash value
Specify the HASHKEYS clause to create a hash cluster and specify the number of hash values for the hash cluster.
---oracle會把hash value相同的記錄儲存在一起
In a hash cluster, Oracle Database stores together rows that have the same hash key value. The hash value for a row
is the value returned by the hash function of the cluster.
---oracle會採用四捨五入的方法計算hashkeys的值,最小值為2;如你忽略index和hashkeys,oracle預設建立index cluster
Oracle Database rounds up the HASHKEYS value to the nearest prime number to obtain the actual number of hash values.
The minimum value for this parameter is 2. If you omit both the INDEX clause and the HASHKEYS parameter, then the
database creates an indexed cluster by default.

----你建立hash cluster,oracle馬上根據size和hashkeys引數值分配空間
When you create a hash cluster, the database immediately allocates space for the cluster based on the values of the
SIZE and HASHKEYS parameters.

SQL> create cluster cluster1(a int) hashkeys 10 hash is mod(a,15);

Cluster created

SQL> select owner,cluster_name,hash_expression from user_cluster_hash_expressions;

OWNER                          CLUSTER_NAME                   HASH_EXPRESSION
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SCOTT                          CLUSTER1                       mod(a,15)

SQL> drop cluster cluster1;

Cluster dropped

---hash is指定自定義的hash function,否則採用oracle預設hash function
SQL> create cluster cluster1(a int) hashkeys 10 hash is mod(a,15) size 512;

Cluster created

SQL> drop cluster cluster1;

Cluster dropped

---single table表示cluster僅含一個table
SQL> create cluster cluster1(a int) hashkeys 10 hash is mod(a,15) size 512 single table;

Cluster created

SQL> create table t_test(a int) cluster cluster1;

create table t_test(a int) cluster cluster1

ORA-00906: missing left parenthesis

SQL> create table t_test(a int) cluster cluster1(a);

Table created

SQL> create table t_test_2(a int,b int) cluster cluster1(a);

create table t_test_2(a int,b int) cluster cluster1(a)

ORA-25136: this cluster can contain only one table

create table進階學習系列(十一)之cluster_續(二)

相關文章