9.6 Constraint

在關聯式資料庫的架構底下，有可能 Table 之間的資料都具有相依性，為了避免 Table 之間的資料錯亂，因此產生了 Constraint 這個東西來約束 Table 的資料，例如在 HR 這個使用者底下，主要的 Table 為 employees ，其它如 departmemts 、 jobs 等 Table 都與其有相關性，例如 employees 的 department_id 這個欄位是關聯到 departments 的 department_id 欄位 、 job_id 欄位則是關聯到 jobs 的 job_id :

Constraint 的種類總共有 Primary Key 、 Foreign Key 、 Not Null 、 Check ，以及 Unique 五種:

• Primary Key: 

具有 Unique 與 Not Null 的特性， Table 所建立的 Primary Key 可以是單一欄位或者是多個欄位組合起來，作為 Primary Key 的欄位資料必須是唯一值且不可為 Null 。

• Foreign Key: 

用來參照其它 Table 的欄位資料所用， Foreign Key 所參照的 Table 欄位必須是 Primary Key 或者是具有 Unique 屬性，被參照具有 Primary (Unique) Key 的 Table 我們稱作 Parent Table ；而建立 Foreign Key 用來參照別人的 Table 我們稱作 Child Table 。作為 Foreign Key 欄位的資料必須是所參照 Primary Key (或 Unique Key) 裡面的資料，否則會出現 ORA-02291: integrity constraint violated - parent key not found 的錯誤，例如 sales 具有 Primary Key (prod_id) ，而 sales_items 則是建立 Foreign Key (prod_id) 關聯到 sales:

如果在 sales_itmes insert 一筆 sales 不存在的資料就會報 ORA-02291 錯誤:

同樣的如果在 Parent Table delete 一筆資料，但此筆資料正存在 Child Table ，此時會報 ORA-02292: integrity constraint violated - child record found:

若是 Foreign Key 建立時賦予 on delete cascade 屬性，則 Parent Table 刪除資料的同時也會把 Child Table 相對應的資料刪除，此時不會有 ORA-02292 錯誤:

最後需要注意的是， Child Table 所用來當 Foreign Key 的欄位必須要建立 Index ，否則在 Table 資料的更新過程中，容易因為獲取不到 SSX lock (Share Row-eXclusive: Mode 5) 從而產生等待。上述範例中， SALES_ITEMS 這個 Child Table 的 prod_id 欄位作為 Foreign Key ，此時就必須為 SALES_ITEMS 的 prod_id 建立 Index 。

• Not Null Constraint:

在 Table 的欄位設定為 Not Null 屬性時，就會幫它加上一個 Not Null Constraint ，表示此欄位的資料不可為 Null 。

• Check Constraint:

用來檢查 Table 的資料必須符合某些條件，否則不能新增進來。例如限制 sales_items 的 uni_price 欄位值必須大於 0 ，在新增一筆 -100 的資料時便會出現 ORA-02290: check constraint violated 的錯誤:

• Unique Constraint:

用來檢查欄位的資料是否為唯一值。在建立 Unique Index 的同時會自動添加上 Unique Constraint 。

Constraint 的建立可以在 Create Table 的時候就一併建上，或者是之後用 alter table 的命令來建立:

SQL> CREATE TABLE sales( prod_id NUMBER ,cust_id NUMBER,time_id DATE,  CONSTRAINT sales_pk PRIMARY KEY(prod_id));     (在 create table 的時候就建立 primary key) SQL> alter table sales add constraint c1 unique(prod_id);     (新建 unique constraint) SQL> alter table sales_items add constraints sales_items_fk FOREIGN KEY(prod_id) REFERENCES sales(prod_id);     (新建 foreign key)

除非是在 Table 建立的時候就已經把 Constraint 建好，否則在新增 Constraint 的時候 Table 可能已經有資料了，因此 Constraint 本身的狀態可以分為 enable / disable 與 validate / novalidate 兩種。 

enable / disable 的狀態表示此 Constraint 是否要檢查新增進來的資料， enable 表示檢查新增的資料 ； disable 表示不檢查。 validate / novalidate 表示 Constraint 是否檢查已經存在 Table 上面的資料， validate 表示在 Constraint 新增的同時，檢查 Table 已存在的資料是否符合此 Constraint 的設定，如果已經有資料違反此新增的規則就無法建立，此時必須使用 novalidate 不檢查已存在的資料來新增:

Constraint 建立的預設選項都是 enable validate 。

如果要修改 Constraint 屬性必須使用 alter table 命令來修改:

SQL> alter table sales_items enable novalidate constraint pri_check;     (將 constraint pri_check 改為 enable novalidate) SQL> alter table sales_items disable validate constraint sales_items_fk;     (將 constraint sales_items_fk 改為 disable validate)

要得知 Constraint 的資訊可以從 dba_constraints 或是 user_constraints 來查詢:

其中的 constraint_type 代表:

• P: Primary Key

• R: Foreign Key

• U: Unique Key

• C: Check 或是 Not Null Constraint

藉由 Constraint 的設定可以確保 Table 之間的資料維持一致性，也可以避免輸入錯誤的資料進到 Table 中，對於關聯式資料庫來說是一個重要的角色。

9.5 Index Organized Table

Oracle Index Organized Table (IOT) 是屬於一個特殊形態的 Table ，它同時具有 Table 與 Index 的特性。 Table 的特性是只有存放資料 (Data) ，而 Index 的特性是除了存放欄位資料外，還同時存放此筆資料所對應的 ROWID ， ROWID 表示資料存放在 Data File 上的實體位置，也因此透過 Index 的查詢會比較快；而 IOT 則是除了存放 Data 外也存放 Key Column 的 ROWID ，是一個同時具有 Table 與 Index 特性的物件。

IOT 必須要有 Primary Key ，而 Key Column 的 ROWID 就存放在本身的 Table 當中，建立 Table 時使用 ORGANIZATION INDEX 就表示建立為 IOT :

SQL> create table useriot (user_id number, user_name varchar2(20), user_dep varchar2(20), constraint pk_id primary key (user_id)) organization index tablespace iot_ts pctthreshold 20 overflow tablespace iot_o_ts;

其中 PCTTHRESHOLD 表示每一筆 Row Data 所要保留 Block 空間的百分比，有點類似於 PCTUSED ，若資料大小超過 Block 所能存放的大小，則 Key Column 存放在原本的 Tablespace ，其餘存放在 Overflow Tablespace。

若一個 Table 的特性是只有建立 Primary Key ，且所應用的查詢都是會用到 Primary Key ，則此 Table 的特性很適合建立成 IOT ，好處是 IOT 本身已經存放 ROWID ，查詢不需要透過 Index 找到 ROWID 後再去迴表反查 Table 的其他資料，效率上會比使用 Index 的 Primary Key 來得好，同時 IOT 不需要再額外建立 Unique Index ，可以減少建立 Index 所消耗的空間。

但若是一個 Table 除了 Primary Key 之外，其它欄位也會建立 Index ，而且查詢不一定會用到 Primary Key ，那麼就不適合建立為 IOT ，在 IOT 上再建立其它 non-unique Index ，使用的效能上不會比一般的 Table 來得好。

9.4 Temporary and External Table

Oracle 資料庫的暫時性表格稱作 Global Temporary Table ，用來存放暫時性的資料所用，使用的是 Temporary Tablespace 空間，特性是當前 session 所存放的資料只有自己這個 session 可以看到，其它人的 session 無法看到這些資料，並且在自己 session 登出後這些資料會自動清除。例如左邊 session 所 insert 的一筆資料，右邊重新登入一個 session 是無法查到此資料的:

建立 Global Temporary Table 有兩種模式:

• on commit preserve rows: Global Temporary Table 中的資料只有在 session 登出後才清除。

• on commit delete rows (預設): Global Temporary Table 中的資料在進行 commit 之後就把資料清除 。

例如建立一個只有在 session 登出後才清除資料的 Global Temporary Table:

SQL> create global temporary table gtmp (user_id number, user_name varchar2(20))         on commit preserve rows;

進行 commit 之後資料仍保留:

如果是使用 on commit delete rows ，則資料在 commit 後就會清除:

SQL> create global temporary table gtmp2 (user_id number, user_name varchar2(20))         on commit delete rows;

Oracle External Table 的特性是將 Table 的資料存放在資料庫之外(放在作業系統上) ，資料型態可以是文字檔(csv格式) 或是 Oracle Datapump 的 dump 檔案，資料庫本身只有存放 Table 的 Meta Data (欄位、資料型態等架構) ， 由於資料是存放在外部，所以 External Table 是不會占資料庫 Segement 的空間，對於一些需要時常更新 Table 資料的作業來說是一個很好用的功能，因為只需要更新存放在外部的檔案即可更新資料，不用再花費時間將 Table 重新 Delete 再 Insert 了。

建立 External Table 前必須先於資料庫裡面建立一個 Directory 來指向作業系統的位置，例如建立一個 Directory 為 extdata 指向作業系統的 /home/oracle 目錄:

SQL> create directory extdata as  '/home/oracle'; SQL> grant read, write on directory extdata to hr;

接下來我們就可以建立一個 Extenal Table:

• 於建立 Table 的語法中加入 organization external 表示建立為 External Table 。

• type oracle_loader 表示外部資料為文字格式，此範例所使用的文字檔為 emp.txt (右上角) ，內容為逗號分隔的資料格式。

• default directory extdata 指定外部資料所存放的位置，這個位置是之前所建立的 Directory extdata ，相當於作業系統上的 /home/oracle 目錄。

• access parameters 用來設定資料庫 Table 與外部檔案資料的對應，這邊設定 fields terminated by ',' 表示外部資料的每個欄位是以逗號來做分隔，然後指定每個逗號分隔欄位是對應於 Table 的哪些欄位。

• 最後的 location 設定為 'emp.txt' 表示要讀取 emp.txt 這個檔案的資料。

External Table 建立好之後，當我們查詢它，便可以得到與 emp.txt 相同的資料內容:

另一種 External Table 的方式是去讀取 Datapump 的 dump 檔，只需將 type 的設定改為 oracle_datapump 即可:

External Table 的另一種應用方式，既然它可以讀取 dump 檔，那麼我們也可以反過來將資料透過 External Table 匯出成為一個 dump 檔，例如我們利用 create table as 的方式將 regions 這個 Table 的資料匯出成一個 dump 檔:

External Table 所需要注意的是， location 所設定的檔名是固定的，因此在更新外部資料的時候需要注意檔案名稱是否相同，如果不同的話就讀不到資料了，一般來說都是將舊的檔案重新命名，然後再更新相同名稱的新檔案進來。

9.3 Permanent Table

Table 是資料庫中最基礎的物件，用來存放資料所用， Oracle 資料庫的 Table 可分為永久性的 (Permanent Table) 、 暫時性的 (Temporary Table) 與外部表格 (External Table) 三種， Permanent Table 即是一般我們存放資料所用的 Table ，其中又可以分為 Partition 與 Non-Partition Table。

Table 的建立使用 create table 命令來建立，只需要給予 Table 名稱以及欄位的 Data Type 即可建立一個 Table，例如建立一個 ordmst 的表格如下:

SQL> create table ordmst (order_no number(10),order_date  date, cust_no number(10), sale_no  number(5),ship_desc   varchar2(50),po_no char(10) not null,keyin_date  date default sysdate);

較完整的語法可以賦予 pctfree 、 pctused ， storage 等參數:

SQL> create table ordmst (order_no number(10),order_date  date, cust_no number(10),  sale_no  number(5),ship_desc   varchar2(50),po_no char(10) not null,keyin_date  date default sysdate)      PCTFREE 10 PCTUSED 80     TABLESPACE data1     STORAGE (INITIAL 50M NEXT 150K MAXEXTENTS 200   PCTINCREASE 0 );

如果不指定 Tablespace ， 則 Table 會建立在使用者預設的 Tablespace 底下，以目前來說，除了要指定 Table 放置的 Tablespace 外，其餘的參數無須指定，使用預設值大多就足夠。

除此之外，也可以使用 create table as 的命令以複製的方式來建立 Table，例如建立一個 Table emp 從 employees 這個表格複製而來:

SQL> create table emp as select * from employees where 1=2;

where 1=2 表示只要複製此表的欄位格式，不複製資料，如果不使用此條件的話則會連同資料一起建立出來。

更改 Table 架構需使用 alter table 命令:

SQL> alter table ordmst add (keyin_user  varchar2(20));      (為 ordmst 這個 table 增加新欄位 keyin_user) SQL> alter table ordmst modify (po_no number);      (將 ordmst 這個 table 的 po_no 欄位改為 number 資料型態) SQL> alter table ordmst rename column order_no to order_number;     (將 order_no 欄位重新命名為 order_number) SQL> alter table ordmst rename to ord_mst;     (將 ordmst 這個 table 重新命名為 ord_mst)

對於 Table 或是欄位，可以使用 comment 命令來將其註解，例如:

SQL> comment on table ordmst is '訂單表格'; (為 table 加上註解) SQL> comment on column ordmst.order_no is '訂單編號'; (為欄位加上註解)

刪除 Table 裡面的資料，可分為 Delete 與 Truncate 兩種， Delete 屬於 DML ，未 commit 的資料可以使用 rollback 進行恢復，資料在 Delete 的過程中會產生 redo / archive log 紀錄； Truncate 屬於 DDL 操作，一旦進行了 truncate 資料就無法 rollback 了。例如:

SQL> delete from emp where employee_id=190; SQL> commit; (刪除 employee_id=190 這筆資料) SQL> delete from emp; SQL> commit; (刪除 emp 這個 table 的所有資料) SQL> truncate table emp; (刪除 emp 這個 table 的所有資料)

如果要刪除整個 Table 的話則是使用 drop table 命令，例如刪除 emp 這個 Table:

SQL> drop table emp;

Oracle 的 Partition Table 類型可以區分如下:

• Range Partition: 以區間來劃分 Partition，一般都是用 Date 或是 Number 欄位的區間來劃分。例如:

SQL> CREATE TABLE sales( prod_id NUMBER ,cust_id NUMBER,time_id DATE,  CONSTRAINT sales_pk PRIMARY KEY(prod_id))  PARTITION BY RANGE (time_id)  ( PARTITION p1 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))     TABLESPACE tsa ,  PARTITION p2 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))     TABLESPACE tsb);

• Hash Partition: 由系統使用 Hash 演算法來切分 Partition ，一般用於欄位的資料無法明顯分辨範圍的時候使用。例如:

SQL> CREATE TABLE dep_hash (dep_id NUMBER(4) NOT NULL, dep_name VARCHAR2(30))      PARTITION BY HASH(dep_name)      (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2);

• List Partition: 直接指定每個 Partition 底下有哪些資料。例如:

SQL> CREATE TABLE sales_by_region (deptno number, deptname varchar2(20),       quarterly_sales number(10, 2),state varchar2(2))    PARTITION BY LIST (state)       (PARTITION northwest VALUES ('OR', 'WA'),        PARTITION southwest VALUES ('AZ', 'UT', 'NM'),        PARTITION northeast VALUES  ('NY', 'VM', 'NJ'),        PARTITION southeast VALUES ('FL', 'GA'),        PARTITION northcentral VALUES ('SD', 'WI'),        PARTITION southcentral VALUES ('OK', 'TX'));

• Composite Partition: 複合式的 Partition Table，可以組合 Range、 Hash 或 List 來一起使用，例如建立一個 Range-Hash 的 Composite Partition:

SQL> CREATE TABLE sales( prod_id NUMBER(6),cust_name VARCHAR2(50),time_id DATE)  PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_name)   SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)  ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))  , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))  , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))  , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))  );

• Interval Partition: 只能使用 Date 或 Number 的資料型態來切分 Partition ，類似於 Range Partition ，在資料超出所設定的範圍時，它可以自動增加新的 Partition ，而 Range Partition 則是必須手動添加。例如:

SQL> CREATE TABLE interval_sales( prod_id NUMBER(6),cust_id NUMBER,time_id DATE)    PARTITION BY RANGE (time_id)    INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))     ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),       PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),       PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),       PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

• System Partition: 不使用任何的 Partition Key 來切分 Partition ，只是單純的把 Table 的 Partition 切分出來，之後由使用者來指定資料要 Insert 至哪一個 Partition 。例如:

SQL> CREATE TABLE syspart (c1 integer, c2 integer) PARTITION BY SYSTEM(   PARTITION p1 TABLESPACE tbs_1,   PARTITION p2 TABLESPACE tbs_2,   PARTITION p3 TABLESPACE tbs_3,   PARTITION p4 TABLESPACE tbs_4);   (建立四個 partition 的 Table) SQL> INSERT INTO syspart PARTITION (p1) VALUES (4,5);   (使用者 insert 資料時必須自己指定要塞入哪個 partition)

• Reference Partition: 兩個 Table 具有 Parent-Child 的關係時 (Primary Key 與 Foreign Key 的關係) ， Child Table 所要切分的Partition 資料是參照 Parent Table 而來。例如在上述 Range Partition 所建立的 sales 這個 Table是以 prod_id 當 primary key 、 time_id 為 partition key ，我們可以建立一個 Child Table ，而它的 Partition 範圍是參照 sales 而來，必須注意的是這邊所使用的 foreign key 必須具有 Not Null 屬性才可以:

SQL> CREATE TABLE sales_items(prod_id NUMBER not null,item_id NUMBER not null ,uni_price NUMBER not null, CONSTRAINT sales_items_fk FOREIGN KEY(prod_id) REFERENCES sales(prod_id))     PARTITION BY REFERENCE(sales_items_fk);

• Virtual Column-Based Partition: 以 Virtual Column 為 Partition Key 所做的 Partition 。例如:

SQL> CREATE TABLE sales( prod_id NUMBER ,cust_id NUMBER,time_id DATE,quantity_sold NUMBER NOT NULL, amount_sold NUMBER NOT NULL, total_amount AS (quantity_sold * amount_sold), CONSTRAINT sales_pk PRIMARY KEY(prod_id))  PARTITION BY RANGE (total_amount)  (PARTITION p_small VALUES LESS THAN (1000), PARTITION p_medium VALUES LESS THAN (5000), PARTITION p_large VALUES LESS THAN (10000), PARTITION p_extreme VALUES LESS THAN (MAXVALUE));

上述 Partition 中， Interval Partition 、 System Partition 、 Reference Partition 與 Virtual Column-Based Partition 都是在 Oracle 11g 之後才有的新功能。

針對 Partition 的操作有 Add 、 Drop 、 Merge 、 Split 、 Truncate 與 Exchange Partition。例如:

SQL> alter table sales add partition p3 values less than (TO_DATE('01-OCT-2006','dd-MON-yyyy'));      (將 sales 新增 partition p3) SQL> alter table sales drop partition p3 update global indexes;      (將 sales 刪除 partition p3) SQL> alter table sales truncate partition p1 update global indexes;      (將 sales 的 partition p1 進行 truncate) SQL> alter table sales merge partitions p2,p3 into partition p3 update global indexes;      (將 sales 的 partition p2,p3 合併為 p3) SQL> alter table sales split partition p3 at (to_date('01-JUL-2006','dd-MON-yyyy')) into (partition p2, partition p3) update global indexes;      (將 sales 的 partition p3 切分為 p2, p3)

Exchange Partition 是用來將一個 Partition 中的資料與另一個非 Partition 的 Table 進行互換，過程中是以 Segment Level 的層級將資料從底層進行替換，替換過程中不論資料大小所花的時間都是非常簡短的，對於需要處理大量資料的 Partition 來說是一個很好用的方法。例如:

SQL> create table sales_ex as select * from sales where 1=2;    (先建立架構與 partition table 相同的 non-partition table : sales_ex) SQL> alter table sales exchange partition p1 with table sales_ex update global indexes;    (將 partition p1 裡面的資料與 sales_ex 這個 table 的資料進行互換)

Partition Table 的用途主要是將資料量大的 Table 劃分為多個區塊，在執行 SQL 語法時只會掃描所需要的區塊，對於處理大資料的效能上有很大的幫助，使用者必須依據 Table 的資料特性選擇要建立哪一種 Partition Table ，目前使用最廣泛的還是所屬 Range Partition 。

要查詢 Table 的相關資訊可以從 dba_tables 中來查詢， Partition Table 則是從 dba_part_tables 與 dba_tab_partitions 來查詢。