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