資料表建立引數介紹（一）

 

建立資料表create table是我們對資料庫進行的常見操作。我們一般使用create table之後，指定了資料列資訊和主鍵等約束資訊，其他就交給Oracle使用預設值了。今天我們一起來看看這些預設值。說明：本片只關注一般資料表，臨時表、聚簇、IOT等特殊型別暫時不考慮。

 

提取完整的DDL

 

首先我們需要提取出建立資料表的完整DDL語句，才能將Oracle提供的預設值們正確抽取出來。此處我們使用dbms_metadata.get_ddl方法。

//源資料抽取指令碼

set serveroutput on size 10000;

set timing on;

 

declare

  c_ddl clob;

begin

  c_ddl := dbms_metadata.get_ddl('TABLE','DEPT','SCOTT');

 

  dbms_output.put_line(c_ddl);

end;

/

//輸出結果

  CREATE TABLE "SCOTT"."DEPT"

   (    "DEPTNO" NUMBER(2,0),

    "DNAME" VARCHAR2(14),

    "LOC" VARCHAR2(13),

     CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"  ENABLE

   )

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE

(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

 

 

上面是使用Oracle自帶的源資料匯出工具，對Scott使用者下的資料表EMP進行提取的DDL語句。

 

 

注意：其上有兩個片段（標註紅色）是相似。這個要說明一下，我們建立一個資料表，在Oracle中要建立資料段data segment物件。同時，如果我們指定了主鍵，Oracle會自動為這個主鍵建立索引，索引是一種索引段index segment物件。所以，如果在建立資料表的時候，也指定了主鍵primary key，那麼一共會生成兩個段segment物件。

 

下面，我們對相似的程式碼引數片段進行分析。

 

1、PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

2、STORAGE

(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

3、TABLESPACE "USERS"

 

 

程式碼段可以分為三個部分，下面分別進行介紹。

 

Part1、資料塊Data Block與表屬性部分

 

標註1部分表示是資料塊結構和使用引數，以及我們的資料表在使用中的一些引數。

 

在Oracle中，資料庫邏輯結構被劃分為“表空間Tablespace、段物件Segment、分割槽extent和塊block”。其中，block是最小的結構物件。

 

ü        PCTFREE：為資料更新準備的“留白”

 

資料終究是要寫入到資料塊裡面的。對資料表中的塊block來說，都會依次填滿行資料。而Oracle寫資料表的順序是首先找到一個空閒塊，之後向空閒塊中寫入資料。

 

那麼Oracle如何判斷這個資料塊是否非空閒呢？就是使用PCTFREE引數了，該引數是一個百分比值，預設為10%。如果一個資料塊空閒的空間低於PCTFREE設定值，就認為這個資料塊已經寫滿。會將這個塊從空閒塊的列表（FREELIST）上取下來。

 

那麼，為什麼要有這個引數？為什麼要保留這10%呢？答案就是為了進行update使用。資料儲存在資料行裡，隨著不斷的更新，每行所佔有的空間是一個不定的範圍。在資料插入之後，如果發生儲存空間增加（比如：varchar2型別字元長度變化），餘下的10%就留作資料行空間延展使用。

 

那麼，如果超過這個10%，還是不能裝下資料怎麼辦？Oracle定位資料使用的物理rowid機制，實際上就是定位特定的資料行在某個資料塊的第幾個slot（槽）上。如果一個資料塊再也裝不下一些資料行，那麼這些資料行就需要另找一個新的資料塊進行儲存。也就意味著這些行有一個新的new-rowid位置。但是，Oracle還會按照原來的old-rowid定位資料行。原來的資料塊上，記錄著該資料行的新位置new-rowid，再次找到新的資料行位置。這個技術過程就稱為行遷移（row migrate）。

 

注意：我們要讀一個資料行的內容。理論上希望訪問的資料塊越少越好，最好只有一個塊。但是，如果發生行遷移，我們就不得不訪問多個資料塊才能得到資料。所以，行遷移是我們通常不希望看到的。解決的方法，就是保留一個適當的PCTFREE值。

 

 

選擇合適的PCTFREE的值要根據系統的性質而定。如果是一個典型的OLTP系統，資料更新操作多，變化大。這時候就需要設定一個略大些的PCTFREE。相反，如果更新很少，大部分都是讀操作，PCTFREE略小些也無礙。

 

 

PCTFREE意味著一種空間的閒置。如果需要進行某種資料表壓縮，設定PCTFREE為0也是一種思路。

 

 

ü        PCTUSED：資料塊的判定容量底線

 

剛才我們談論PCTFREE的時候，介紹了Oracle在寫入資料的時候，是將新資料寫入到資料塊中，直到認為已經滿了後，再尋找新塊寫入。這個過程相反的是，如果一個過去寫滿的資料塊，經過若干次刪除後，會逐漸變空，那麼什麼時間點才能認為這個塊是一個空閒塊，能接受新的資料插入呢？

 

這就是PCTUSED引數的用途。如果一個資料塊的使用容量低於PCTUSED設定的限制，那麼就認為這個資料塊已經空閒，可以放置回FREELIST列表中，作為空閒資料塊接受新資料行的插入。

 

 

一般是不需要調節這個引數的，筆者認為，頻繁的資料塊空閒或者寫滿切換，是有損於資料庫效能的。所以，設定預設的40%一般是可以接受的。對一些資料變化巨大，刪除頻繁的系統，這個引數可以配合PCTFREE統一籌劃。

 

 

ü        INITRANS和MAXTRANS

 

初始事務INITRANS和最大事務MAXTRANS是在資料塊級別的引數。Oracle行級鎖是Oracle最大的特點之一。資料庫事務的本質還是對資料塊的修改，而事務的資訊是記錄在資料塊頭。

 

引數INITRANS的作用就是表示資料塊上可以標記的初始事務數目。如果同時進行的事務資料量超過這個數量，事務數目可以增加，直到達到MAXTRANS的限制。

 

 

從效能角度看，我們不希望一個資料塊同時進行過多的事務。因為這樣起碼意味著資料塊過熱。所以，建議設定一個合理的INITRANS。