oracle 索引聚簇表的工作原理

標籤：pac   ret   介紹   一個   完成   軟體   alter   cot   select   

Richard-Lui

一：首先介紹一下索引聚簇表的工作原理：（先建立簇，再在簇裡建立索引，建立表時指定列的簇類型）

   聚簇是指：如果一組表有一些共同的列，則將這樣一組表格儲存體在相同的資料庫塊中；聚簇還表示把相關的資料存放區在同一個塊上。利用聚簇，一個塊可能包含多個表的資料。概念上就是如果兩個或多個表經常做連結操作，那麼可以把需要的資料預先儲存在一起。聚簇還可以用於單個表，可以按某個列將資料分組儲存。

   更加簡單的說，比如說，EMP表和DEPT表，這兩個表格儲存體在不同的segment中，甚至有可能儲存在不同的TABLESPACE中，因此，他們的資料一定不會在同一個BLOCK裡。而我們有會經常對這兩個表做關聯查詢，比如說：select * from emp,dept whereemp.deptno = dept.deptno.仔細想想，查詢主要是對BLOCK的操作，查詢的BLOCK越多，系統IO就消耗越大。如果我把這兩個表的資料聚集在少量的BLOCK裡，查詢效率一定會提高不少。

   比如我現在將值deptno=10的所有員工抽取出來，並且把對應的部門資訊也儲存在這個BLOCK裡（如果存不下了，可以為原來的塊串聯另外的塊）。這就是索引聚簇表的工作原理。

   二：建立過程。

   索引聚簇表是基於一個索引聚簇（index cluster）建立的。裡面記錄的是各個聚簇鍵。聚簇鍵和我們用得做多的索引鍵不一樣，索引鍵指向的是一行資料，聚簇鍵指向的是一個ORACLE BLOCK。我們可以先通過以下命令建立一個索引簇。

   SQL> conn scott/tiger

    已串連。

   SQL> desc dept

   名稱                                     是否為空白? 類型

  ----------------------------------------- ------------------------------------

    DEPTNONOT NULL NUMBER(2)

    DNAMEVARCHAR2(14)

    LOCVARCHAR2(13)

   SQL> create cluster emp_dept_cluster

    2 (deptno number(2) )

    3 size1024

    4 /

    簇已建立。

   這個名字可以使用者定義，不一定叫deptno，資料類型必須和需要使用這個聚簇的資料類型一致NUMBER(2)。在這裡最關鍵的一個參數是size。這個選項原來告訴Oracle：我們希望與每個聚簇索引值關聯大約1024位元組的資料（1024對於一般的表一條資料沒問題），oracle會在用這個資料庫塊上設定來計算每個塊最多能放下多少個聚簇鍵。假設塊大小為8KB，Oracle會在每個資料庫塊上放上最多7個聚簇鍵，也就是說，對應部門10、20、30、40、50、60和70的資料會放在一個塊上，一旦插入部門80，就會使用一個新塊。存放的資料是和插入順序相關的。

    因此，SIZE測試控制著每塊上聚簇鍵的最大個數。這是對聚簇空間利用率影響最大的因素。如果把這個SIZE設定得太高，那麼每個塊上的鍵就會很少（單位BLOCK可以存的聚簇鍵就少了），我們會不必要地使用更多的空間。如果設定得太低，又會導致資料過分串鏈（一個聚簇鍵不夠存放一條資料），這又與聚簇本來的目的不符，因為聚簇原本是為了把所有相關資料都儲存在一個塊上。

   向聚簇中放資料之前，需要先對聚簇建立索引。可以現在就在聚簇中建立表，但是由於我們想同時建立和填充表，而有資料之前必須有一個聚簇索引，所以我們先來建立聚簇索引。

   聚簇索引的任務是拿到一個聚簇索引值，然後返回包含這個鍵的塊的塊地址。實際上這是一個主鍵，其中每個聚簇索引值指向聚簇本身中的一個塊。因此，我們請求部門10的資料時，Oracle會讀取聚簇鍵，確定相應的塊地址，然後讀取資料。聚簇鍵索引如下建立：

   SQL> create index emp_dept_cluster_idx

    2 oncluster emp_dept_cluster

    3 /

   索引已建立。

   現在可以建立表了：

   SQL> conn segment_study/liugao

    已串連。

   SQL> create table dept

   2  ( deptno number(2) primary key,3   dname  varchar2(14),

    4 locvarchar2(13)

    5 )

    6 clusteremp_dept_cluster(deptno)

    7 /

    表已建立。

   SQL> create table emp

   2  (empno    numberprimary key,3   ename   varchar2(10),4   job     varchar2(9),5   mgr     number, 6   hiredate date,7   sal     number, 8   comm    number,

    9 deptnonumber(2) constraint emp_fk references dept(deptno)

    10 )

    11cluster emp_dept_cluster(deptno)

    12 /

    表已建立。

   我們可以通過一下SQL語句查看建立：

   SQL> select cluster_name, table_name

    2 fromuser_tables

    3 wherecluster_name is not null

    4 orderby 1;

   CLUSTER_NAME TABLE_NAME

  -----------------------------------------------------------

   EMP_DEPT_CLUSTER DEPT

   EMP_DEPT_CLUSTER EMP

   現在，聚簇，聚簇索引，聚簇索引表都已經建立完成。

   三：載入資料。

   向聚簇索引表中載入資料是個很講究的事情，處理方法不對，會使得聚簇的功能發揮不完全，降低查詢效能。

    方法1：

   首先，我增加一個很大的列char（1000）,加這個列是為了讓EMP行遠遠大於現在的大小。使得一個1024的聚簇無法儲存一行記錄。不能加varchar2(1000),因為ORACLE對varchar2儲存的原則是能省就省，如果資料資料不到1000，不會分配1000的空間的。char則是有多少用多少。呵呵。

   SQL> begin

   2     for x in ( select * from scott.dept )

    3loop

    4 insertinto dept

   5         values ( x.deptno, x.dname, x.loc );

    6 insertinto emp

    7 select*

   8           from scott.emp9          where deptno = x.deptno;

    10 endloop;

    11end;

    12 /

    begin

    *

   第1行出現錯誤:

   ORA-02032:聚簇表無法在簇索引建立之前使用

   ORA-06512:在line 4

   SQL> create index emp_dept_cluster_idx

    2 oncluster emp_dept_cluster

    3 ;

   索引已建立。

   SQL> alter table emp disable constraint emp_fk;

    表已更改。

   SQL> truncate cluster emp_dept_cluster;

    簇已截斷。

   SQL> alter table emp enable constraint emp_fk;

    表已更改。

   SQL> alter table emp add data char(1000);

    表已更改。

   上面的執行錯誤說明聚簇表無法在簇索引建立之前使用。

   首先我們通過先載入emp表，後載入dept表的方式。

   SQL> insert into dept

   2  select * from scott.dept;已建立4行。

   SQL> insert into emp

   2  select emp.*, ‘*‘ from scott.emp;已建立14行。

   然後做一個查詢，通過dbms_rowid.rowid_block_number可以查看此資料所在的BLOCKID,如果dept和emp儲存的行資料不是一個BLOCK ID ,則標記一個‘*‘.查詢結果如下：

   SQL> select dept_blk, emp_blk,2        case when dept_blk <> emp_blk then‘*‘ end flag,

    3deptno

    4 from(

   5  selectdbms_rowid.rowid_block_number(dept.rowid) dept_blk,6        dbms_rowid.rowid_block_number(emp.rowid) emp_blk,7        dept.deptno 8   from emp, dept 9   whereemp.deptno = dept.deptno

    10 )

    11 orderby deptno

    12 /

    DEPT_BLKEMP_BLK F DEPTNO

  ---------- ---------- - ----------

    85 86 *10

    85 86 *10

    85 87 *10

    85 8520

    85 87 *20

    85 86 *20

    85 8520

    85 86 *20

    85 8530

    85 86 *30

    85 8530

    DEPT_BLKEMP_BLK F DEPTNO

  ---------- ---------- - ----------

    85 86 *30

    85 8530

    85 8530

   已選擇14行。

   我們發現，通過先插入emp資料，再插入dept資料，導致大部分的emp和dept的資料都不在一個block上，這不是我們使用聚簇索引的目的。

oracle 索引聚簇表的工作原理