SQL> select cluster_name,tablespace_name from user_clusters;
CLUSTER_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CLUSTER1 TBS_HANG
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
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.
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.
----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.
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
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
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/