oracle 聚簇表學習

-----2010-03-19
昨天的實驗。證明了cluster中的表都是在同一個段上的，但是從實驗中也看到了資料不在同一個塊上的情況。雖然得出了這個結論，但是昨天回家看書了之後，發現原來我的實驗沒有真正按照聚簇表的聚集方式去存放資料。按照如下方法：

begin
for i in (select rownum rn, dbms_random.string('X', 10) a, mod(dbms_random.value(1,10),2) b, '' d
  from dual connect by rownum <= 10) loop
    insert into student values(i.rn, i.a, i.b, i.d);
    insert into stu_grade
    select i.rn, dbms_random.string('X', 10), dbms_random.value(1,100), ''
      from dual;
end loop;
end;
我們便可以模擬出在同一個塊上的情況了：
SQL> select dbms_rowid.rowid_block_number(rowid) blkno,
  2         dbms_rowid.rowid_row_number(rowid) rowno,
  3         s.*
  4    from student s;
 
     BLKNO      ROWNO                                      ID NAME                           
---------- ---------- --------------------------------------- -------------------------------
     42020          0                                       1 X9FCEQT0SX                         
     42024          0                                       2 1QWUGV92YL                        
     42028          0                                       3 K60A3B901E                          
     42032          0                                       4 5SAEMB7IRG                         
     42036          0                                       5 RQ2TOSCU5S                       
     42040          0                                       6 XKJONRUF72                        
     42044          0                                       7 C5J6OE1I2C                          
     42048          0                                       8 NZO3G6ETBC                        
     42052          0                                       9 OGY5HRFLIS                         
     42056          0                                      10 AWQ7QBE79G                      
 
10 rows selected
SQL> select dbms_rowid.rowid_block_number(rowid) blkno,
  2         dbms_rowid.rowid_row_number(rowid) rowno,
  3         g.*
  4    from stu_grade g;
 
     BLKNO      ROWNO                                  STU_ID STU_NAME           
---------- ---------- --------------------------------------- -------------------------------
     42020          0                                       1 MNZU2K6R7R                       
     42024          0                                       2 AB1X3ZJBBN                          
     42028          0                                       3 E6FEVDN9JY                         
     42032          0                                       4 OA0QNIMDO3                        
     42036          0                                       5 BUU69NPUAU                       
     42040          0                                       6 11U7QUPPTF                        
     42044          0                                       7 A1PGQC7EVY                        
     42048          0                                       8 8DO1K7X3ZZ                         
     42052          0                                       9 IIQKD8GT5D                           
     42056          0                                      10 1BKJH1LT53                         
 
10 rows selected
 從以上的結果中，很清楚的能夠看到兩個表的資料所存放的都對應的在同一個塊上。因此昨天的實驗前面的結論是不全面的，只要能夠按照聚簇表要求的存放方式存放，便能夠將資料放到一個塊上。
另：以上實驗修改了昨天的聚簇表欄位，昨天是id和name，今天為了實驗方便，改成了id欄位。

--###########################################

聚簇表可以將多個表捆綁在一起，它是一種資料結構，在這個結構中，
聚簇將一個或多表都放到同一個塊或段中。
使得聚簇中的表在透過聚簇劍關聯查詢時能夠減少對塊的頻繁獲取。
下面是實驗：

--首先建立聚簇
create cluster my_cluster(
id int, name varchar2(100)
);

--建立聚簇表
create table student(
    id int,
    name varchar2(100),
    sex int,
    addr varchar2(100)
)cluster my_cluster(id, name);

create table stu_grade(
    stu_id int,
    stu_name varchar2(100),
    grade varchar2(100),
    class_name varchar2(100)
)cluster my_cluster(stu_id, stu_name);

--建立聚簇索引
create index my_clu_idx on cluster my_cluster tablespace fund_index nologging;
這裡需要提到的是，建立聚簇表後如果沒有建立該聚簇索引，則無法插入資料，執行時會報如下錯誤：
ORA-02032: 聚簇表無法在簇索引建立之前使用
建立該索引後，該問題解決。

insert into student
select rownum, dbms_random.string('X', 10), mod(dbms_random.value(1,10),2), ''
  from dual connect by rownum <= 10;

insert into stu_grade
select trunc(dbms_random.value(1,10)), dbms_random.string('X', 10), dbms_random.value(1,100), ''
  from dual connect by rownum <= 10;

雖然說聚簇表的資料是放在同一個塊或段上的，但是我檢視了下兩個表的所有行所在的塊，沒有發現有聚合的塊：
select dbms_rowid.rowid_block_number(rowid) blkno, s.*
  from student s;

select dbms_rowid.rowid_block_number(rowid) blkno, g.*
  from stu_grade g;

但是在進行段查詢的時候，發現單獨檢視兩個表的段資訊是查不到的：
select * from dba_segments s
 where s.segment_name in('STU_GRADE', 'STUDENT');
可以證明他們的資料是放在同一個段上的，即my_cluster上。

檢視段的資訊，觀察段所包含的區和塊的範圍：
SQL> select dbms_rowid.rowid_block_number(rowid) blkno
  2    from student s;
 
     BLKNO
----------
       161
       165
       169
       173
       177
       181
       185
       189
       193
       197
 
10 rows selected
SQL> select dbms_rowid.rowid_block_number(rowid) blkno
  2    from stu_grade g;
 
     BLKNO
----------
       142
       146
       150
       154
       158
       162
       166
       170
       174
       178
 
10 rows selected
 
SQL>
SQL> select d.BLOCK_ID, d.BLOCK_ID + d.BLOCKS end_block
  2    from dba_extents d where d.segment_name = 'MY_CLUSTER'
SQL> /
 
  BLOCK_ID  END_BLOCK
---------- ----------
       137        265
 可發現，聚簇中的兩個成員表所有的block均落在這個範圍中。

--刪除聚簇表及級聯的表和約束
drop cluster my_cluster including tables cascade constraints ;