1. Table 建置與參數修改

建置Table
在建置Table前需要先確認三件事：
  1. 所使用的帳號是否擁有”CREATE TABLE”權限。使用SYSTEM帳號授予”CREATE TABLE”權限語法如下：
GRANT CREATE TABLE TO TEST_ACCOUNT;
  1. 使用某個TABLESPACE的使用權限。使用SYSTEM帳號授予TABLESPACE的使用權限語法如下：權限語法如下：
ALTER USER TEST_ACCOUNT QUOTA UNLIMITED ON TEST_TABLESPACE;
  1. 如果要建置別的帳號下面的表格就需要”CREATE ANY TABLE”的權限。使用SYSTEM帳號授予”CREATE ANY TABLE”權限語法如下：
GRANT CREATE ANY TABLE TO TEST_ACCOUNT;
以SQL*PLUS建置語法參數如下
CREATE TABLE [schema.] table
(column datatype [ , column datatype ] ...)
[TABLESPACE tablespace ]
[ PCTFREE integer ]
[ PCTUSED integer ]
[ INITRANS integer ]
[ MAXTRANS integer ]
[ STORAGE storage-clause ]
[LOGGING | NOLOGGING]
[PCTFREE]：此參數是針對區段階層（Segment Level）做的設定，每一個Data Block的空間需要保留多少空間給未來的修改（Update）資料所使用，假設PCTFREE是10所以就是10% X BLOCK SIZE如果BLOCK SIZE是4K就是代表這個Segment上的Data Block要保留400個bytes的空間，如果此時只剩下400bytes的空間時，此Data Block就不允許再繼續做任何新增（Insert）資料的動作，因為Data Block的動作會造成此Data Block的Header往下長，Data往上長，如此Free space就會減少，而剩下的空間是提供給未來在此資料區塊裡面的資料修改（Update）所使用的，因此當到達PCTFREE所設定的值時是無法做新增（Insert），但可以修改（Update）和刪除（Delete），刪除（Delete）的動作會讓data往下減少要減少到PCTUSED就可以再繼續作新增（Insert）了。PCTFREE注意事項如下：
  • 設定較大的PCTFREE時可以減少資料遷徒（Row Migration）的發生，但會增加硬碟空間的浪費，也就是說當PCTFREE加大時會降低資料區塊（Data Block）的緊密度，因為預留的空間太多了。資料遷徒（Row Migration）
  • 計算PCTFREE公式：（Average Row Size – Initial Row Size）* 100/Average Row Size
    • Average Row Size：指在這table上的平均每一筆資料的大小。
    • Initial Row Size：指一開始一筆資料的大小。
  • PCTFREE是留給未來資料修改（Update）所使用的，但在何種情況下需要較大的PCTFREE呢？主要原因有以下兩點：
    • 當有欄位一開始設定是NULL值，預留以後才會新增資料進去時使用。
    • 當有欄位的大小增加。
  • PCTFREE的預設值是10。
[PCTUSED]：當使用者執行刪除（Delete）或是修改（Update）的動作時，此Data Block裡面的data部份會減少，當減少到PCTUSE的標準就可以再作新增（Insert）的動作了。
  • 設定較大的PCTUSED參數可以減少磁碟上的消耗，但有可能會增加資料遷徒（Row Migration）。
  • 計算PCTUSED公式：100 – PCTFREE(Average Row Size/Available Data Space)
  • 以上的資料可由DBA_TABLES裡面的AVG_ROW_LEN欄位得到相關資訊，但你必須先分析（Analyze）表格（Table）才能得到相關的資訊。
  • PCTUSED的預設值是40。
PCTFREE與PCTUSER運作狀況如圖
[INITRANTS]：指當資料庫一開啟時或Table剛建置好時，一開始時可以同時讓幾個連線使用這個Table，當連線使用需求增加時，再逐步增加許同時使用的數量，一直到達MAXTRANS參數的設定。預設值是1，最大值是255。
[MAXTRANS]：最多可以同時讓幾個連線使用這個Table。預設值是255，最大值是255。
[STORAGE storage_clause ]： 此參數主要是設定當建置Table時所有儲存的方式與參數，如果沒設定Storage的參數就會往上找上一個階層的儲存參數，而Table的上一個階層就是Tablespace。儲存參數內容如下：
DEFAULT STORAGE ( INITIAL integer[K|M]
                     NEXT integer[K|M]
                     MINEXTENTS integer
                     MAXEXTENTS integer
                     PCTINCREASE integer
                     BUFFER POOL[DEFAULT | KEEP| RECYCLE]  
  • INITIAL：初始擴充區塊（Initial Extent），指Table一建置起來時開始要先規劃多大Extent的空間。如果沒有設定會使用此Table所在的Tablespace上的設定。預設值是5個資料區塊（Data Block）。
  • NEXT：下一個擴充區塊（Next Extent），指Table當空間不夠用時要自動擴充多大的Extent。如果沒有設定會使用此Table所在的Tablespace上的設定。預設值是5個資料區塊（Data Block）。
  • MINEXTENTS：指此表格最少要有多少個擴充區塊（Extent）。如果在Tablespace上有設MINIMUM EXTENT時，也就是說在這個Tablespace上的擴充區塊（Extent）的個數不能小於這個參數的倍數，如果建置Table時NEXT參數小於Tablespace的MINIMUM EXTENT這個參數時，MINIMUM EXTENT這個參數會忽略掉TableNEXT參數，而採用Tablespace的MINIMUM EXTENT這個參數。預設值是1。

  • MAXEXTENTS：指限制此表格（Table）最大只能擴展多少個擴充區塊（Extent），預設是沒有限制，配置直到表格（Table）所在的Tablespace使用完為止。
  • PCTINCREASE：此參數式設定下一個分配出來的Extent比前一個Extent大出多少百分比，公式是NEXT * （1 + PCTINCREASE/100），PCTINCREASE預設值是50%。。舉個例子當建置一個Table時如果INITIAL的設定是1000K，而NEXT的設定也是1000K，所以Oracle一開始會先給此Table1000K擴充區塊（Extent），第二個Extent也是1000K，但第三個Extent就會用到PCTINCREASE所以是1000 * (1 + 50/100)因此依照上面的參數，當我們Create table出來時就已經有了3500K。也就是說所擴增的擴充區段是等比級數的增加。
  • BUFFER POOL[DEFAULT | KEEP| RECYCLE]：將Database Buffer Cache分為三塊，Default、Keep與Recycle，此參數就是指定將此表格是要使用哪種Pool。預設是Default Pool。

[LOGGING | NOLOGGING]：是指在特殊情形之下當新增（Insert）、刪除（Delete）或是修改（Update）此資料表（Table）可以不產生線上重作日誌（Online REDO LOG）。此目的是可以增加資料新增（Insert）、刪除（Delete）或是修改（Update）得速度，但缺點是當資料庫損毀時此資料表（Table）的資料都無法回復，因為沒有產生線上重作日誌（Online REDO LOG）。預設是LOGGING。
建置表格的範例如下：

CREATE TABLE TEST.TEST_TABLE
     (AA VARCHAR2(10),
      BB VARCHAR2(10) NOT NULL,
      CC VARCHAR2(10),
      DD VARCHAR2(10) NOT NULL)  
    TABLESPACE TEST_TABLESPACE
                 PCTFREE 20 PCTUSED 40
                 INITRANS 1
                 MAXTRANS 255
    STORAGE ( INITIAL 64K
               NEXT 64K
               MINEXTENTS 1
               MAXEXTENTS UNLIMITED
               PCTINCREASE 2
               BUFFER_POOL KEEP) ;

修改表格儲存參數

修改表格儲存參數，語法範例如下：

ALTER TABLE TEST.TEST_TABLE
PCTFREE 40
PCTUSED 20
STORAGE (NEXT 1M
          MINEXTENTS 3
           MAXEXTENTS 200);

  • 變更的儲存參數只有目的有兩個，增加空間的使用效能與減少migration的機率。

  • NEXT：如果手動更改NEXT的參數，現有的EXTENT是不會變，但下一個EXTENT才會變。
  • MINEXTENTS：如果你手動更改MINEXTENTS的參數，除非目前EXTENT的個數等於或是小於你所想要變更的個數，才會生效，要不然只有當把Table truncate掉才會生效，記住只有truncate才會生效，如果只是delete，原來分配的EXTENT還會存在。
  • INITIAL參數是不能改的。
  • 減少migration的機率是指PCTFREE設小一點，把PCTUSED設大一點，如此可把空間填滿一點。
  • PCTFREE：那當變更PCTFREE參數什麼時候會生效呢？只有當這個BLOCK從Free List移出來再回到Free List之後才會生效，假設PCTFREE從10%變成20%，那何時才會生效呢？就是當有一個Block剩下10%，會從Free List中移走，所以那個Block不會受到影響，只有當他降到了PCTUSED時才能被insert，也就是說當重新返回Free list裡面的時候，那個新的PCTFREE參數才會生效，或者對對已經存在Free list裡面的block才會生效。
  • PCTUSED：是馬上生效。
  • INITRANS：只對新的block生效。
  • MAXTRANS：對所有的block都生效。
如果只需要針對單一儲存參數做修改語法範例如下
ALTER TABLE TEST.TEST_TABLE  PCTFREE 20;
標籤：

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)