Oracle索引總結（五）- Oracle索引種類之表簇索引（cluster index），oraclecluster

Oracle索引總結（五）- Oracle索引種類之表簇索引（cluster index），oraclecluster

表簇索引（cluster index）

對於表簇索引而言，必須使用表簇。

由於簇索引與索引表簇關聯緊密，無法單獨拿出來總結，因此一並進行總結。

1.1 表簇的定義

• 表簇是一組通過相同公共列（簇鍵），構成的表的集合。
• 如上圖，右側獨立的兩張表，employees員工表與departments部門表，通過簇鍵department_id列，構成了左側的一個表簇（cluster）。
• 當構成表簇後，一個單獨的數據塊會包含多個表的數據行信息。

1.2 表簇的分類

• 對於oracle數據庫，主要支持兩種表簇：索引聚簇以及哈希聚簇

1.3 索引聚簇與哈希聚簇定位數據的區別

• 索引聚簇：oracle使用簇索引，將簇鍵鍵值與相應數據所在數據塊地址（DBA）關聯進行數據定位。
• 哈希聚簇：oracle使用散列函數替代索引，計算出相應數據的物理存儲位置，減少了針對索引塊的I/O，進而實現了更快地定位。

1.4 表簇的意義

• 通過簇鍵的關聯，將不同表的相關數據行盡可能地存儲在相同的數據塊上，不但可以減少存儲數據時使用的空間，而且可以降低數據訪問時的磁盤I/O數量，提高訪問速度。

1.5 表簇的優點

• 對於表簇，在物理層面上，盡可能將與簇鍵關聯的數據，存儲在相同的數據塊，進而減少塊調用時的磁盤I/O操作（非簇關系的表，如emp表跟dept表中的各自行數據，一定不在同一個數據塊。塊調用時讀取更多的塊。原因：不同的segments，segment-extents-blocks）。
• 對於表簇，簇鍵的鍵值無論在被簇表中出現幾次，數據塊中，只存儲一次，且與相關數據行一並存儲，減少了占用的空間。

1.6 表簇使用的注意事項

• 對於存在大量DML操作的表，不適合建立表簇。

• 對於需要經常進行全表掃描的表，不適合建立表簇。（不再像非簇表那樣，一個數據塊中僅包含一張表中的數據，還包含了與簇鍵相關的其他表數據行的數據，這意味著掃描簇中單獨的一張表，需要掃描更多的數據塊）

• 對於需要頻繁進行TRUNCATE操作的表，不適合建立表簇。（簇表中無法針對單獨的被簇表進行truncate操作）

     此外，對於哈希表簇，不用也不能創建索引

1.7 表簇索引

• 表簇索引，即B-tree簇索引，由B-tree結構構成。主要針對索引表簇（index cluster）
• 與普通B-tree索引的不同在於，普通B-tree索引的索引鍵將鍵值與數據的rowid進行關聯。聚簇索引的索引鍵值與相應數據所在數據塊的地址（DBA）相關聯。

1.8 關於索引表簇（index cluster）創建

• 創建的順序如下：建立簇 - 建立簇表 - 建立簇索引 - 加載數據。
• 創建簇的參數SIZE，決定了每個簇鍵值可以關聯多少字節的數據，進而計算出每個數據塊能容納多少個簇鍵。
  • 當SIZE設置過高，單獨的數據塊可以容納的簇鍵會減少，且對於單個簇鍵會占用比實際需求更多的空間，造成空間的浪費。
  • 當SIZE設置過低，單個的簇鍵無法在單獨的數據塊中容納一條完整的數據，進而導致溢出數據部分串聯至新塊，影響聚合度。
  • 當SIZE設置為1024時，對於一個8K（8192）的標准塊，由於數據塊的pct_free，實際可容納7個簇鍵。
• 對於索引表簇，當不創建簇索引時，無法進行數據的加載。

• 下面進行創建的演示，及cluster、index、cluster table在數據存儲上的一些區別的說明。
• 紅色字體為創建的關鍵語句
• 綠色字體為步驟說明
• 綠色粗體字體為對象存儲分布的一些特點的說明

• --創建一個表簇
  Yumiko@Sunny >create cluster clu_info_employee (deptno number) size 1024;
  Cluster created.
  
  
  
  Yumiko@Sunny >select * from tab where TNAME like '%CLU%';
  
  TNAME                          TABTYPE  CLUSTERID
  ------------------------------ ------- ----------
  CLU_INFO_EMPLOYEE              CLUSTER
  
  
  
  --通過查詢user_clusters視圖（或者dba_clusters視圖），可以看到創建的表簇為index cluster，且SIZE設置為1024
  Yumiko@Sunny >select cluster_name, tablespace_name, cluster_type, key_size from user_clusters;
  
  CLUSTER_NAME                   TABLESPACE_NAME                CLUST   KEY_SIZE
  ------------------------------ ------------------------------ ----- ----------
  CLU_INFO_EMPLOYEE              USERS                          INDEX       1024
  
  
  
  
  --通過user_objects視圖（或者dba_objects視圖），同樣可以查閱cluster的信息。
  --需要注意，同一個cluster下的對象，其DATA_OBJECT_ID的值一致。
  Yumiko@Sunny >select OBJECT_ID,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE from user_objects where OBJECT_NAME like '%CLU%';
  
   OBJECT_ID OBJECT_NAME               DATA_OBJECT_ID OBJECT_TYPE
  ---------- ------------------------- -------------- -------------------
       52626 CLU_INFO_EMPLOYEE                  52626 CLUSTER
  
  
  
  
  --通過user_clu_columns視圖可以看到，此時未顯示剛剛創建的cluster信息，表明該簇目前為空簇。
  Yumiko@Sunny >select * from USER_CLU_COLUMNS;
  no rows selected
  
  
  
  --此處應該注意到，通過dba_segments視圖查看，雖然當前是空簇，但已出現剛剛創建的cluster，證明此時已占用了空間。
  Yumiko@Sunny >select SEGMENT_NAME,SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where owner='SCOTT' and segment_name like '%CLU%';
  
  SEGMENT_NAME          SEGMENT_TYPE    EXTENTS HEADER_FILE HEADER_BLOCK    BYTES   BLOCKS
  ----------------------------------------------------------------------------------------
  CLU_INFO_EMPLOYEE     CLUSTER               1           4          395    65536        8
  
  
  
  --創建簇表
  Yumiko@Sunny >create table clu_info_dept(DEPTNO number,DNAME VARCHAR2(14),LOC VARCHAR2(13)) cluster CLU_INFO_EMPLOYEE(deptno);
  Table created.
  
  
  
  
  Yumiko@Sunny >create table clu_info_emp(DEPTNO number,ENAME VARCHAR2(10),JOB VARCHAR2(9)) cluster CLU_INFO_EMPLOYEE(deptno);
  Table created.
  
  
  
  
  --查詢user_clu_columns視圖可以注意到，此時出現了簇及簇表的相應信息，說明此時，簇已不再是空簇。
  Yumiko@Sunny >select * from USER_CLU_COLUMNS;
  
  CLUSTER_NAME             CLU_COLUMN_NAME         TABLE_NAME              TAB_COLUMN_NAME
  -----------------------------------------------------------------------------------------
  CLU_INFO_EMPLOYEE        DEPTNO                  CLU_INFO_DEPT           DEPTNO
  CLU_INFO_EMPLOYEE        DEPTNO                  CLU_INFO_EMP            DEPTNO
  
  
  
  
  
  Yumiko@Sunny >select * from tab where TNAME like '%CLU%';
  
  TNAME                          TABTYPE  CLUSTERID
  ------------------------------ ------- ----------
  CLU_INFO_DEPT                  TABLE            1
  CLU_INFO_EMP                   TABLE            2
  CLU_INFO_EMPLOYEE              CLUSTER
  
  
  
  
  --同上面所說，查詢user_objects視圖可以看到簇的所有信息，此外可以注意到DATA_OBJECT_ID列是一致的，如前所說
  Yumiko@Sunny >select OBJECT_ID,OBJECT_NAME,DATA_OBJECT_ID,OBJECT_TYPE from user_objects where OBJECT_NAME like '%CLU%';
  
   OBJECT_ID OBJECT_NAME               DATA_OBJECT_ID OBJECT_TYPE
  ---------- ------------------------- -------------- -------------------
       52627 CLU_INFO_DEPT                      52626 TABLE
       52628 CLU_INFO_EMP                       52626 TABLE
       52626 CLU_INFO_EMPLOYEE                  52626 CLUSTER
  
  
  
  
  --查詢此時的dba_segments視圖，並未發現添加的兩張簇表
  Yumiko@Sunny >select SEGMENT_NAME,SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where owner='SCOTT' and segment_name like '%CLU%';
  
  SEGMENT_NAME        SEGMENT_TYPE   EXTENTS HEADER_FILE HEADER_BLOCK    BYTES  BLOCKS
  ------------------------------------------------------------------------------------
  CLU_INFO_EMPLOYEE   CLUSTER              1           4          395    65536       8
  
  
  
  
  --查詢此時的dba_tables視圖，卻可以發現剛剛建立的表
  Yumiko@Sunny >select TABLE_NAME,CLUSTER_NAME,STATUS from dba_tables where owner='SCOTT' and table_name like '%CLU%';
  
  TABLE_NAME                     CLUSTER_NAME                   STATUS
  ------------------------------ ------------------------------ --------
  CLU_INFO_EMP                   CLU_INFO_EMPLOYEE              VALID
  CLU_INFO_DEPT                  CLU_INFO_EMPLOYEE              VALID
  
  
  
  
  
  --嘗試加載數據失敗，報錯明顯提示了未建立簇索引，也符合之前說過的創建順序
  Yumiko@Sunny >insert into clu_info_dept select * from dept;
  insert into clu_info_dept select * from dept
              *
  ERROR at line 1:
  ORA-02032: clustered tables cannot be used before the cluster index is built
  
  
  
  
  -- 創建簇索引，注意此時的關鍵字on cluster
  Yumiko@Sunny >create index CLU_INFO_index on cluster CLU_INFO_EMPLOYEE;
  Index created.
  
  
  
  
  --查詢此時的dba_segments視圖，同樣有索引的segments信息。
  Yumiko@Sunny >select SEGMENT_NAME,SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where owner='SCOTT' and segment_name like '%CLU%';
  
  SEGMENT_NAME        SEGMENT_TYPE    EXTENTS HEADER_FILE HEADER_BLOCK    BYTES   BLOCKS
  --------------------------------------------------------------------------------------
  CLU_INFO_INDEX      INDEX                 1           4          403    65536        8
  CLU_INFO_EMPLOYEE   CLUSTER               1           4          395    65536        8
  
  
  
  --加載數據
  Yumiko@Sunny >insert into clu_info_dept select * from dept;
  4 rows created.
  
  
  
  
  --再次查詢dba_segments視圖，依然沒有cluster table的信息
  Yumiko@Sunny >select SEGMENT_NAME,SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where owner='SCOTT' and segment_name like '%CLU%';

  SEGMENT_NAME        SEGMENT_TYPE    EXTENTS HEADER_FILE HEADER_BLOCK    BYTES   BLOCKS
  --------------------------------------------------------------------------------------
  CLU_INFO_INDEX      INDEX                 1           4          403    65536        8
  CLU_INFO_EMPLOYEE   CLUSTER               1           4          395    65536        8
  
  

•  從以上不難看出，對於表簇，cluster table僅僅存儲在cluster中，不會成為單獨的segment占用空間。

1.9 關於索引表簇（index cluster）刪除

• drop cluster cluster_name                                                           -- 用於刪除空簇
• drop cluster cluster_name including tables                                     -- 用於刪除非空簇
• drop cluster cluster_name including tables cascade constraints        --用於刪除非空簇及外鍵約束

    此外，對於簇表本身的刪除，按照普通表方法即可。

1.10 哈希表簇（hash cluster）簡介

下面引用oracle官方文檔的例子：

CREATE CLUSTER call_detail_cluster (
telephone_number NUMBER,
call_timestamp NUMBER SORT,
call_duration NUMBER SORT )
HASHKEYS 10000
HASH IS telephone_number
SIZE 256;

其中：
HASH IS參數：

• 該參數可選，用於指明進行散列的列，可以不明確指定。
• 當目標列數據類型為number類型，且可以唯一標識行時，可以將該列指定為散列值。
• 不指明該參數時，oracle使用內部散列函數。

HASHKEYS參數：

• 該參數用於指定和限制散列函數可以產生的唯一的散列值的數量。
• 該參數越小，相對關聯的數據塊越多，發生的物理讀越多。

SIZE參數：

• 該參數基本含義與索引聚簇相似。

• 該參數越大，雖然單獨的數據塊可以容納的簇鍵會減少，甚至可能會由於單個簇鍵占用比實際需求更多的空間造成空間的浪費，但由於有更多的空間存儲更多的相關數據，只要設置合理，一
  定程度上卻可以降低物理讀。