Oralce里的cluster

来源：互联网发布：阿里云独立上市编辑：程序博客网时间：2024/05/01 04:04

简单介绍

http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT608

table cluster就是两个或多个表在相同块中共同拥有一列或多列数据。就是说一个数据块中包含了来自多个表中的数据。此时dump块可以发现tabs !=!。

cluster key 就是创建table cluster指定的共享的列。

indexed cluster 就是 tablecluster使用的索引。indexed cluster就是在cluster key 上定义的b-tree索引。

hash cluster很像indexed cluster，但它在创建时，createcluster没有指定hashkeys。它无需单独索引存在，数据即是索引。

hash cluster空间分配不同于indexedcluster，hashkeys 指定了可能存在的多少个key值，size指定每个值大小。

适用范围

使用 table cluster好处在于多表联接时可以降低逻辑读。从而提高SQL效率。也可以一定程度上，降低磁盘空间使用。但它不适用于经常更新的表、或经常需要走全表扫描的表，及需要truncate的表等。

hash cluster 使用于经常需要查询但较少更新的表，hashkey经常用作等值查询条件。但不支持范围查询。

使用简介

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5001.htm#BABDBDEE

create cluster语法

physical_attributes_clause::=

parallel_clause ::=

语法说明：

其中需要格外注意的是size和hashkeys。

Oracle计算空间：HASHKEYS * SIZE/ database_block_size

hashkeys 指定了可能存在的多少个key值，size指定每个值大小。例，块大小4096

CREATE CLUSTER employees_departments_cluster   (department_id NUMBER(4))SIZE 8192 HASHKEYS 100;

则Oracle分配至少200个块。（8192*100/4096=200）。但oracle并不强制限定hashkeys，例如hashkeys设置成100，依然可以插入200个不同的department_id，但此时效率会有影响。多个key值可能共享一个hash值，会产生hash 冲突。key值更多时，会使用额外的块，效率也会有所影响，此时建议重建cluster。

sort关键字只在 hash cluster中有效。但此时不支持Rowdependency。

cluster最大支持32个表。但不支持LOB。

最大支持16个 clusterkey 列，列的类型大小必须在clusteredtables中一致。

实验

环境

SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE   11.2.0.3.0      ProductionTNS for Linux: Version 11.2.0.3.0 -ProductionNLSRTL Version 11.2.0.3.0 – ProductionSQL> show userUSER is "YANGL"

SQL> col comments for a50SQL> col table_name for a40SQL> select * from dict where table_namelike '%CLUSTER%'; TABLE_NAME                               COMMENTS------------------------------------------------------------------------------------------ALL_CLUSTERS                             Description ofclusters accessible to the userALL_CLUSTER_HASH_EXPRESSIONS             Hash functions for all accessibleclustersUSER_CLUSTERS                            Descriptions ofuser's own clustersUSER_CLUSTER_HASH_EXPRESSIONS            Hash functions for the user's hashclustersDBA_HIST_CLUSTER_INTERCON                Cluster Interconnect HistoricalStatsDBA_CLUSTERS                             Description of allclusters in the databaseDBA_CLUSTER_HASH_EXPRESSIONS             Hash functions for all clustersV$CLUSTER_INTERCONNECTS                  Synonym forV_$CLUSTER_INTERCONNECTSGV$CLUSTER_INTERCONNECTS                 Synonym forGV_$CLUSTER_INTERCONNECTS 9 rows selected.SQL> desc DBA_CLUSTERS Name                                                 Null?    Type ------------------------------------------------------------- ------------------------------------ OWNER                                                NOT NULL VARCHAR2(30) CLUSTER_NAME                                          NOTNULL VARCHAR2(30) TABLESPACE_NAME                                       NOT NULLVARCHAR2(30) PCT_FREE                                                      NUMBER PCT_USED                                                      NUMBER KEY_SIZE                                                       NUMBER INI_TRANS                                            NOT NULL NUMBER MAX_TRANS                                            NOT NULL NUMBER INITIAL_EXTENT                                                NUMBER NEXT_EXTENT                                                   NUMBER MIN_EXTENTS                                           NOTNULL NUMBER MAX_EXTENTS                                           NOTNULL NUMBER PCT_INCREASE                                                   NUMBER FREELISTS                                                     NUMBER FREELIST_GROUPS                                               NUMBER AVG_BLOCKS_PER_KEY                                            NUMBER CLUSTER_TYPE                                                   VARCHAR2(5) FUNCTION                                                      VARCHAR2(15) HASHKEYS                                                      NUMBER DEGREE                                                        VARCHAR2(20) INSTANCES                                                     VARCHAR2(20) CACHE                                                         VARCHAR2(10) BUFFER_POOL                                                   VARCHAR2(7) FLASH_CACHE                                                   VARCHAR2(7) CELL_FLASH_CACHE                                              VARCHAR2(7) SINGLE_TABLE                                                  VARCHAR2(10) DEPENDENCIES                                                   VARCHAR2(8)

--创建clusterSQL> CREATE CLUSTER cluster_test 2     (id3 NUMBER(2)) 3  SIZE 512 ; Cluster created. SQL> selectsegment_name,segment_type,extent_id,bytes,blocks from user_extents wheresegment_name = 'CLUSTER_TEST'; SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID      BYTES    BLOCKS-------------------- ---------------------------- ---------- ----------CLUSTER_TEST         CLUSTER                     0      65536          8 SQL> selectcluster_name,key_size,cluster_type,hashkeys,single_table from user_clusterswhere cluster_name = 'CLUSTER_TEST'; CLUSTER_NAME                     KEY_SIZE CLUST   HASHKEYS SINGLE_TAB------------------------------ --------------- ---------- ----------CLUSTER_TEST                          512 INDEX          0    N --建立测试表SQL> create table t1  2  (id1 number(1), 3   id3 number(2) 4  )  5 cluster cluster_test (id3);Table created.SQL> create table t2 2  (id2 number(1), 3   id3 number(2) 4  )  5 cluster cluster_test (id3);Table created. --此时插入数据报错。SQL> insert into t1 values(1,2);insert into t1 values(1,2)           *ERROR at line 1:ORA-02032: clustered tables cannot be usedbefore the cluster index is built--创建索引完成，插入数据成功。 SQL> create index indx_cluste_test oncluster cluster_test; Index created. SQL> insert into t1 values(1,2); 1 row created. SQL> commit; Commit complete.

--简单实验 hash cluster--清理环境SQL> drop table t1;Table dropped.SQL> drop table t2;Table dropped.SQL> drop cluster cluster_test;SQL> CREATE CLUSTER test_hash_cluster (id NUMBER(2))  2     SIZE 512 SINGLE TABLE HASHKEYS 100;Cluster created.SQL> create table t   2  ( id NUMBER(2))  3  cluster test_hash_cluster (id);Table created.SQL> insert into t select rownum from dual connect by rownum <100;99 rows created.SQL> commit;Commit complete.SQL> set autotrace traceonly;SQL> exec dbms_stats.gather_table_stats('YANGL','T',method_opt=>'for all columns size auto');PL/SQL procedure successfully completed.--执行计划 TABLE ACCESS HASHSQL> select * from t where id =50;Execution Plan----------------------------------------------------------Plan hash value: 2719348108--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     3 |     1   (0)| 00:00:01 ||*  1 |  TABLE ACCESS HASH| T    |     1 |     3 |     1   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("ID"=50)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          3  consistent gets          0  physical reads          0  redo size        416  bytes sent via SQL*Net to client        419  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

0 0