時間：2015-06-28 00:00:00 來源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評論(1)
一：首先介紹一下索引聚簇表的工作原理：
聚簇是指：如果一組表有一些共同的列，則將這樣一組表存儲在相同的數(shù)據(jù)庫塊中；聚簇還表示把相關(guān)的數(shù)據(jù)存儲在同一個塊上。利用聚簇，一個塊可能包含多個表 的數(shù)據(jù)。概念上就是如果兩個或多個表經(jīng)常做鏈接操作，那么可以把需要的數(shù)據(jù)預先存儲在一起。聚簇還可以用于單個表，可以按某個列將數(shù)據(jù)分組存儲。
更加簡單的說，比如說，EMP表和DEPT表，這兩個表存儲在不同的segment中，甚至有可能存儲在不同的TABLESPACE中，因此，他們的數(shù)據(jù) 一定不會在同一個BLOCK里。而我們有會經(jīng)常對這兩個表做關(guān)聯(lián)查詢，比如說：select * from emp,dept where emp.deptno = dept.deptno .仔細想想，查詢主要是對BLOCK的操作，查詢的BLOCK越多，系統(tǒng)IO就消耗越大。如果我把這兩個表的數(shù)據(jù)聚集在少量的BLOCK里，查詢效率一定 會提高不少。
比如我現(xiàn)在將值deptno=10的所有員工抽取出來，并且把對應的部門信息也存儲在這個BLOCK里（如果存不下了，可以為原來的塊串聯(lián)另外的塊）。這 就是索引聚簇表的工作原理。
二：創(chuàng)建過程。
索引聚簇表是基于一個索引聚簇（index cluster）創(chuàng)建的。里面記錄的是各個聚簇鍵。聚簇鍵和我們用得做多的索引鍵不一樣，索引鍵指向的是一行數(shù)據(jù)，聚簇鍵指向的是一個ORACLE BLOCK。我們可以先通過以下命令創(chuàng)建一個索引簇。
SQL> conn scott/tiger
已連接。
SQL> desc dept
名稱? 是否為空? 類型
—————————————– ——– —————————-
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> create cluster emp_dept_cluster
2 ( deptno number(2) )
3 size 1024
4 /
簇已創(chuàng)建。
這個名字可以用戶定義，不一定叫deptno，數(shù)據(jù)類型必須和需要使用這個聚簇的數(shù)據(jù)類型一致NUMBER(2)。在這里最關(guān)鍵的一個參數(shù)是size。這 個選項原來告訴Oracle：我們希望與每個聚簇鍵值關(guān)聯(lián)大約1024字節(jié)的數(shù)據(jù)（1024對于一般的表一條數(shù)據(jù)沒問題），Oracle會在用這個數(shù)據(jù)庫 塊上設(shè)置來計算每個塊最 多能放下多少個聚簇鍵。假設(shè)塊大小為8KB，Oracle會在每個數(shù)據(jù)庫塊上放上最多7個聚簇鍵，也就是說，對應部門10、20、30、40、50、60 和70的數(shù)據(jù)會放在一個塊上，一旦插入部門80，就會使用一個新塊。存放的數(shù)據(jù)是和插入順序相關(guān)的。
因 此，SIZE測試控制著每塊上聚簇鍵的最大個數(shù)。這是對聚簇空間利用率影響最大的因素。如果把這個SIZE設(shè)置得太高，那么每個塊上的鍵就會很少（單位 BLOCK可以存的聚簇鍵就少了），我們會不必要地使用更多的空間。如果設(shè)置得太低，又會導致數(shù)據(jù)過分串鏈（一個聚簇鍵不夠存放一條數(shù)據(jù)），這又與聚簇本 來的目的不符，因為聚簇原本是為了把所有相關(guān)數(shù)據(jù)都存儲在一個塊上。
向聚簇中放數(shù)據(jù)之前，需要先對聚簇建立索引。可以現(xiàn)在就在聚簇中創(chuàng)建表，但是由于我們想同時創(chuàng)建和填充表，而有數(shù)據(jù)之前必須有一個聚簇索引，所以我們先來 建立聚簇索引。
聚簇索引的任務是拿到一個聚簇鍵值，然后返回包含這個鍵的塊的塊地址。實際上這是一個主鍵，其中每個聚簇鍵值指向 聚簇本身中的一個塊。因此，我們請求部門10的數(shù)據(jù)時，Oracle會讀取聚簇鍵，確定相應的塊地址，然后讀取數(shù)據(jù)。聚簇鍵索引如下創(chuàng)建：
SQL> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster
3 /
索引已創(chuàng)建。
現(xiàn)在可以創(chuàng)建表了：
SQL> conn segment_study/liugao
已連接。
SQL> create table dept
2 ( deptno number(2) primary key, 3??? dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno)
7 /
表已創(chuàng)建。
SQL> create table emp
2 ( empno??? number primary key, 3??? ename??? varchar2(10), 4??? job????? varchar2(9), 5??? mgr????? number, 6??? hiredate date, 7??? sal????? number, 8??? comm???? number,
9 deptno number(2) constraint emp_fk references dept(deptno)
10 )
11 cluster emp_dept_cluster(deptno)
12 /
表已創(chuàng)建。
我們可以通過一下SQL語句查看創(chuàng)建：
SQL> select cluster_name, table_name
2 from user_tables
3 where cluster_name is not null
4 order by 1;
CLUSTER_NAME TABLE_NAME
—————————— —————————–
EMP_DEPT_CLUSTER DEPT
EMP_DEPT_CLUSTER EMP
現(xiàn)在，聚簇，聚簇索引，聚簇索引表都已經(jīng)建立完成三：加載數(shù)據(jù)。向聚簇索引表中加載數(shù)據(jù)是個很講究的事情，處理方法不對，會使得聚簇的功能發(fā)揮不完全，降低查詢性能。
方法1：
首先，我增加一個很大的列char（1000）,加這個列是為了讓EMP行遠遠大于現(xiàn)在的大小。使得一個1024的聚簇無法存儲一行記錄。不能加 varchar2(1000),因為ORACLE對varchar2存儲的原則是能省就省，如果數(shù)據(jù)數(shù)據(jù)不到1000，不會分配1000的空間的。 char則是有多少用多少。呵呵。
SQL> begin
2????? for x in ( select * from scott.dept )
3 loop
4 insert into dept
5??? values ( x.deptno, x.dname, x.loc );
6 insert into emp
7 select *
8 ??? from scott.emp 9???? where deptno = x.deptno;
10 end loop;
11 end;
12 /
begin
*
第1行出現(xiàn)錯誤:
ORA-02032:聚簇表無法在簇索引建立之前使用
ORA-06512:在line 4
SQL> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster
3 ;
索引已創(chuàng)建。
SQL> alter table emp disable constraint emp_fk;
表已更改。
#p#副標題#e#
SQL> truncate cluster emp_dept_cluster;
簇已截斷。
SQL> alter table emp enable constraint emp_fk;
表已更改。
SQL> alter table emp add data char(1000);
表已更改。
上面的執(zhí)行錯誤說明聚簇表無法在簇索引建立之前使用。
首先我們通過先加載emp表，后加載dept表的方式。
SQL> insert into dept
2 select * from scott.dept;已創(chuàng)建4行。
SQL> insert into emp
2 select emp.*, ‘*’ from scott.emp;已創(chuàng)建14行。
然后做一個查詢，通過dbms_rowid.rowid_block_number可以查看此數(shù)據(jù)所在的BLOCK ID,如果dept和emp存儲的行數(shù)據(jù)不是一個BLOCK ID ,則標記一個’*’.查詢結(jié)果如下：
SQL> select dept_blk, emp_blk, 2?? case when dept_blk <> emp_blk then ‘*’ end flag,
3 deptno
4 from (
5 select dbms_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?? where emp.deptno = dept.deptno
10 )
11 order by deptno
12 /
DEPT_BLK EMP_BLK F DEPTNO
———- ———- – ———-
85 86 * 10
85 86 * 10
85 87 * 10
85 85 20
85 87 * 20
85 86 * 20
85 85 20
85 86 * 20
85 85 30
85 86 * 30
85 85 30
DEPT_BLK EMP_BLK F DEPTNO
———- ———- – ———-
關(guān)鍵詞標簽：Oracle,聚簇表
相關(guān)閱讀
熱門文章
Oracle中使用alter table來增加,刪除,修改列的語法
oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例
誤刪Oracle數(shù)據(jù)庫實例的控制文件
為UNIX服務器設(shè)置Oracle全文檢索
人氣排行 oracle中使用SQL語句修改字段類型-oracle修改SQL語句案例 Oracle中使用alter table來增加,刪除,修改列的語法 ORACLE SQL 判斷字符串是否為數(shù)字的語句 ORACLE和SQL語法區(qū)別歸納(1) oracle grant 授權(quán)語句 ORACLE修改IP地址后如何能夠使用 如何加速Oracle大批量數(shù)據(jù)處理 Oracle刪除表的幾種方法