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 來查詢。