程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle中從Clustering Factor說起

Oracle中從Clustering Factor說起

編輯：Oracle數據庫基礎

近日在看《Oracle 9i & 10g編程藝術：深入數據庫體系結構》這本書的時候，發現一個叫做Clustering Factor的概念，該因子用於反映利用索引進行區間掃描的成本，說得可能有點抽象，我們做一個有趣的實驗說明一下吧：

第一步，在Oracle中建立一個表Source，該表很簡單，就兩個字段，腳本如下：

create table Source 
(
 x int ,
 y int 
);

第二步，向Source裡面插入100萬條記錄，其中x從1到1000000中按順序取值，而y則是以隨機數。

begin
    for i in 1 .. 1000000
    loop
         insert into source(x,y) values ( i , to_char(dbms_random.random,'99999999999999999') );
    end loop;
end;
commit;

第三步，基於Source表的數據，再創建兩張表tab01、tab02，並為這兩個表的x字段添加索引，最後分析統計一下信息。

建表語句如下：

create table tab01 as select * from source;
create table tab02 as select * from source order by y; 
create index idx01 on tab01(x); 
create index idx02 on tab02(x); 
begin 
  dbms_stats.gather_table_stats( user , 'tab01' , cascade=>true ); 
  dbms_stats.gather_table_stats( user , 'tab02' , cascade=>true ); 
end;

第四步，運行兩個查詢語句（將Autotrace打開）。

select avg(y/(x+1)) from tab01 where x between 10000 and 30000;
select avg(y/(x+1)) from tab02 where x between 10000 and 30000;

這兩個查詢查詢非常簡單，執行的速度也很快，我在普通PC機中的虛擬機（有點拗口）上面執行用了不到1秒就搞定了。

但是，這兩個查詢的執行計劃，還有consistent gets的數字是不一樣的。

第一個查詢：

game@ora10g(Oracle01) SQL> select avg(y/(x+1)) from tab01 where x between 10000 and 30000;

Elapsed: 00:00:00.02

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    12 |   143   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE              |       |     1 |    12 |            |          |
|   2 |   TABLE Access BY INDEX ROWID| TAB01 | 19971 |   234K|   143   (1)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | IDX01 | 20005 |       |    48   (3)| 00:00:01 | **********
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        116  consistent gets
          0  physical reads

第二個查詢

game@ora10g(Oracle01) SQL> select avg(y/(x+1)) from tab02 where x between 10000 and 30000;

Elapsed: 00:00:00.04

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    12 |   566   (5)| 00:00:07 |
|   1 |  SORT AGGREGATE    |       |     1 |    12 |            |          |
|*  2 |   TABLE Access FULL| TAB02 | 20145 |   236K|   566   (5)| 00:00:07 |**********
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2417  consistent gets
          0  physical reads

大家應該發現，兩個語句的執行計劃不一樣，已經查詢成本也不一樣了吧，我們再使用提示，強制使第二個查詢使用索引 idx02 ，得到如下結果。

game@ora10g(Oracle01) SQL> select /*+ index( tab02 idx02 ) */ avg(y/(x+1)) from tab02 where x between 10000 and 30000;

Elapsed: 00:00:00.05

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    12 | 20203   (1)| 00:04:03 |
|   1 |  SORT AGGREGATE              |       |     1 |    12 |            |          |
|   2 |   TABLE Access BY INDEX ROWID| TAB02 | 20145 |   236K| 20203   (1)| 00:04:03 |
|*  3 |    INDEX RANGE SCAN          | IDX02 | 20145 |       |    48   (3)| 00:00:01 |**********
--------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        136  recursive calls
          0  db block gets
      20058  consistent gets
          0  physical reads

從執行時間上來說，沒看出多大的問題，但是從 consistent gets 來說，我們給Oracle發出了一個“錯誤”的提示。

為什麼同樣的數據同樣的數據結構，卻有如此大的差異呢？細心的你，可能已經發現我們的建表語句存在著一定的差異。

create table tab01 as select * from source;
create table tab02 as select * from source order by y;

tab01中的行是按照Source表中行的順序排列的，相鄰的行幾乎是在同一個數據塊中，而tab02的行是按照y列排序的，也就是亂須的，簡單的說tab01和tab02雖然數據是相同的，但是物理組織上是不相同的。

在物理組織上的差異造成了這麼大的性能差異令我非常驚訝，我們再看看兩個索引的填充因子是什麼？

執行以下查詢：

select 
    idx.index_name, 
    tab.table_name,
    tab.num_rows,
    tab.blocks,
    idx.clustering_factor
from 
    user_indexes idx inner join user_tables tab
on idx.table_name = tab.table_name
order by table_name;

我這裡的結果是：

INDEX_NAME           TABLE_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR
-------------------- ------------------------------ ---------- ---------- -----------------
IDX01                TAB01                              995426       2459              4723
IDX02                TAB02                             1003471       2459            999596

當Clustering Factor的值越高，進行索引區間掃描的成本越高，物理組織上更加“零散”。

這個例子可以從某種程度上解析：“為什麼同一份數據在不同機器上跑，性能不一樣？”。

如此類推，我覺得同樣的實驗，在SQL Server上也應該可以重演。於是類似地，我做了如下的一個實驗：

第一步，建表，語句沒變：

create table Source 
(
 x int ,
 y int 
);

第二步，向 Source 表裡面插入實驗數據，100萬條。

begin tran;
declare @n int;
set @n = 0;
while @n < 1000000
begin 
 insert into source(x,y) values ( @n , checksum(newid()) );
 set @n=@n+1;
end;
commit;

第三步，建表，建索引

select * into tab01 from Source;
select * into tab02 from Source order by x;
create index idx01 on tab01(x); 
create index idx02 on tab02(x);

第四步，運行兩個查詢語句（ set statistics time on; set statistics io on; ）。

select avg(y/(x+1)) from tab01 where x between 10000 and 11000;
select avg(y/(x+1)) from tab02 where x between 10000 and 11000;

由於 10000 到 30000 的取值范圍SQL Server的查詢優化器使用了表掃描，所以特意改了一下。

查詢計劃和統計信息卻是一樣的。

image_thumb5 統計信息都是：

表 'tab01'。掃描計數 1，邏輯讀取 1007 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 'tab02'。掃描計數 1，邏輯讀取 1007 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

看來Oracle的那一套理論不適合於SQL Server中啊。

引用Thomas Kyte在《Oracle 9i & 10g編程藝術：深入數據庫體系結構》中的一句話：

首先，所有數據庫本質上都不同。其次，為一個新的數據庫平台設計應用時，對於數據庫如何工作不能做任何假設。學習每一個新數據庫時，應該假設自己從未使用過數據庫。在一個數據庫中能做的事情在另一個數據庫中可能沒有必要做，或者根本不能做。

我從04年開始寫.net程序，剛開始的時候發現 C# 和 Java 多麼相似（哥寫的不是java，寫的是C#），總覺得那堆Java 寫的 Spring、Hibernate 框架 C# 都能做出來，不久NHibernate，IBatis.net 出來了。慢慢的隨著代碼寫多了，對.net的感覺轉變為“.net 好用，Java不好用，所以搞.Net的人，懶得搞框架”。前段時間看了大牛 Jeffrey Zhao 的我對NHibernate的感受（1）：對延遲加載方式的誤解和我對NHibernate的感受（2）：何必到處都virtual 今天才大有感悟，學習一種語言不應被另一種語言禁锢思想，學習語言的最高境界應該是弄清什麼事情在一種語言中能做，而在另一種語言中可能沒有必要做或者根本不能做，而不是糾纏在哪種更有前途的問題上，大牛即使沒有達到這種境界也差不了多少啦。