create table进阶学习系列(十一)之cluster_续(二)

最新推荐文章于 2022-05-10 22:51:00 发布

clg10051

最新推荐文章于 2022-05-10 22:51:00 发布

阅读量139

点赞数

文章标签：数据库

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-753249/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-753249/