本文测试cluster table

---语法
CLUSTER cluster (column [, column ]...)

---cluster子句语义
CLUSTER Clause

---cluster标明创建表是cluster一部分; column是创建表的列且要和cluster column对应;cluster column是由构成主键的列或部分列组成;
The CLUSTER clause indicates that the table is to be part of cluster. The columns listed in this clause are the table columns
that correspond to the cluster columns. Generally, the cluster columns of a table are the column or columns that make up its primary
key or a portion of its primary key. Refer to CREATE CLUSTER for more information.---报错cluster不存在,先要创建cluster

--- 未完全理解
Specify one column from the table for each column in the cluster key. The columns are matched by position, not by name.

---cluster table它使用cluster的存储分配信息,因此不能使用pctfree,pctused,initrans,tablespace
A cluster table uses the space allocation of the cluster. Therefore, do not use the PCTFREE, PCTUSED, or INITRANS parameters,
the TABLESPACE clause, or the storage_clause with the CLUSTER clause.

--cluster table操作限制
Restrictions on Cluster Tables Cluster tables are subject to the following restrictions:

---对象表或包含lob列的表不能是cluster的一部分
Object tables and tables containing LOB columns or columns of the Any* Oracle-supplied types cannot be part of a cluster.

----你不能为cluster table指定parallel或cache
You cannot specify the parallel_clause or CACHE or NOCACHE for a table that is part of a cluster.

----不能指定rowdependencies或norowdependencies,除非cluster创建指定了相同的owdependencies或norowdependencies配置信息
You cannot specify CLUSTER with either ROWDEPENDENCIES or NOROWDEPENDENCIES unless the cluster has been created with the same
ROWDEPENDENCIES or NOROWDEPENDENCIES setting.

---报错因cluster不存在
SQL> create table t_test(a int) cluster cluster1 (a);

create table t_test(a int) cluster cluster1 (a)


ORA-00943: cluster does not exist

SQL>

---cluster的两种类型:
---cluster也是一种对象类型,和table一样.它如同容器一般,可包含多个table
Use the CREATE CLUSTER statement to create a cluster. A cluster is a schema object that contains data from one or more tables.

------cluster的第一种类型叫index cluster,必须包含多个表;且每个表有一个或多个列;
An index cluster must contain more than one table, and all of the tables in the cluster have one or more columns in common.
---oracle把cluster中所有表的记录存储在一起,共享相同的键值列;
Oracle Database stores together all the rows from all the tables that share the same cluster key.

-------cluster第二种类型是hash cluster,它包含1或多个表,oracle把这些表的记录存储在一起,它们拥有相同的hash key value
In a hash cluster, which can contain one or more tables, Oracle Database stores together rows that have the same hash key value.

------create cluster语法
CREATE CLUSTER [ schema. ] cluster
(column datatype [ SORT ]
    [, column datatype [ SORT ] ]...
)
[ { physical_attributes_clause
    | SIZE size_clause
    | TABLESPACE tablespace
    | { INDEX
      | [ SINGLE TABLE ]
        HASHKEYS integer [ HASH IS expr ]
      }
    }...
]
[ parallel_clause ]
[ NOROWDEPENDENCIES | ROWDEPENDENCIES ]
[ CACHE | NOCACHE ] ;

---创建cluster
SQL> create cluster cluster1(a int) size 512 storage(initial 100k next 50k);

Cluster created

--创建cluster index
SQL> create index idx_cluster1 on cluster cluster1;

Index created

---添加table到cluster,cluster最多可包含32个表
SQL> create table t_test(a int) cluster cluster1 (a);

Table created

---lob列的表不能添加到cluster中
SQL> create table t_test_lob(a int,b blob) cluster cluster1(a);

create table t_test_lob(a int,b blob) cluster cluster1(a)

ORA-03001: unimplemented feature

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

create table t_test_lob(a int,b clob) cluster cluster1(a)

ORA-03001: unimplemented feature

---查询cluster信息
SQL> desc user_clusters;
Name               Type         Nullable Default Comments
------------------ ------------ -------- ------- -----------------------------------------------------------------
CLUSTER_NAME       VARCHAR2(30)                  Name of the cluster
TABLESPACE_NAME    VARCHAR2(30)                  Name of the tablespace containing the cluster
PCT_FREE           NUMBER       Y                Minimum percentage of free space in a block
PCT_USED           NUMBER       Y                Minimum percentage of used space in a block
KEY_SIZE           NUMBER       Y                Estimated size of cluster key plus associated rows
INI_TRANS          NUMBER                        Initial number of transactions
MAX_TRANS          NUMBER                        Maximum number of transactions
INITIAL_EXTENT     NUMBER       Y                Size of the initial extent in bytes
NEXT_EXTENT        NUMBER       Y                Size of secondary extents in bytes
MIN_EXTENTS        NUMBER                        Minimum number of extents allowed in the segment
MAX_EXTENTS        NUMBER                        Maximum number of extents allowed in the segment
PCT_INCREASE       NUMBER       Y                Percentage increase in extent size
FREELISTS          NUMBER       Y                Number of process freelists allocated in this segment
FREELIST_GROUPS    NUMBER       Y                Number of freelist groups allocated in this segment
AVG_BLOCKS_PER_KEY NUMBER       Y                Average number of blocks containing rows with a given cluster key
CLUSTER_TYPE       VARCHAR2(5) Y                Type of cluster: b-tree index or hash
FUNCTION           VARCHAR2(15) Y                If a hash cluster, the hash function
HASHKEYS           NUMBER       Y                If a hash cluster, the number of hash keys (hash buckets)
DEGREE             VARCHAR2(40) Y                The number of threads per instance for scanning the cluster
INSTANCES          VARCHAR2(40) Y                The number of instances across which the cluster is to be scanned
CACHE              VARCHAR2(20) Y                Whether the cluster is to be cached in the buffer cache
BUFFER_POOL        VARCHAR2(7) Y                The default buffer pool to be used for cluster blocks
FLASH_CACHE        VARCHAR2(7) Y                The default flash cache hint to be used for cluster blocks
CELL_FLASH_CACHE   VARCHAR2(7) Y                The default cell flash cache hint to be used for cluster blocks
SINGLE_TABLE       VARCHAR2(20) Y                Whether the cluster can contain only a single table
DEPENDENCIES       VARCHAR2(8) Y                Should we keep track of row level dependencies?

ITPub博客

create table进阶学习系列(十一)之cluster

最新文章