本文測試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?

create table進階學習系列(十一)之cluster

相關文章