Oracle中的暫存資料表、外部表格和分區表，

Oracle中的暫存資料表、外部表格和分區表，
Oracle中的暫存資料表、外部表格和分區表暫存資料表

在Oracle中，暫存資料表是“靜態”的，它與普通的資料表一樣只需要一次建立，其結構從建立到刪除的整個期間都是有效。相對於其他類型的表，暫存資料表只有在使用者實際向表中添加資料時，才會為其分配空間，並且分配的空間來自暫存資料表空間。這就避免了與永久對象的資料爭用儲存空間。

建立暫存資料表的文法如下：

CREATE GLOBAL TEMPORARY TABLE table_name(    column_name data_type,[column_name data_type,...])ON COMMIT DELETE|PRESERVE ROWS;

由於暫存資料表儲存的資料只在當前交易處理或者會話進行期間有效
因此，暫存資料表分為事務級暫存資料表和會話級暫存資料表。

事務級暫存資料表

建立事務級暫存資料表，需要使用ON COMMIT DELETE ROWS子句，事務級暫存資料表的記錄在每次提交事務後被自動刪除。

例1：

CREATE GLOBAL TEMPORARY TABLE tbl_user_transcation(       ID NUMBER,       uname VARCHAR2(10),       usex VARCHAR2(2),       ubirthday DATE)ON COMMIT DELETE ROWS;

會話級暫存資料表

建立會話級暫存資料表，需要使用ON COMMIT PRESERVE ROWS子句，會話級暫存資料表的記錄在使用者與伺服器中斷連線後被自動刪除。

例2：

CREATE GLOBAL TEMPORARY TABLE tbl_user_session(       ID NUMBER,       uname VARCHAR2(10),       usex VARCHAR2(2),       ubirthday DATE)ON COMMIT PRESERVE ROWS;

操作暫存資料表

對事務級暫存資料表插入一條資料但不COMMIT事務：

INSERT INTO tbl_user_transcation VALUES(1,'siege','M',TO_DATE('1991-02-28','YYYY-MM-DD'));SELECT * FROM tbl_user_transcation;

此時，查詢結果如下：

1 siege M 28/02/1991

若進行了COMMIT，則此時表中無資料，說明Oracle已經將資料刪除了。

對事務級暫存資料表插入一條資料：

INSERT INTO tbl_user_session VALUES(1,'siege','M',TO_DATE('1991-02-28','YYYY-MM-DD'));SELECT * FROM tbl_user_session;COMMIT;

此時即使提交了事務，tbl_user_session 中仍有資料。
此時當關閉session後（斷開資料庫連接），再串連資料庫，查詢時則無資料了。

註：在PL/SQL Developer預設配置為開啟一個視窗，即重建立立一個session，因此要注意設定共用session，

外部表格

外部表格是Oracle提供的、可讀取作業系統的檔案系統中儲存的資料的一種唯讀表。外部表格中的資料存放區在作業系統的檔案系統中，只能讀，不能修改。

建立外部表格

先以SYSDBA身份登入，授予使用者相關許可權：

  GRANT CREATE ANY DIRECTORY TO siege;

然後以使用者身份登入建立目錄：

   CREATE DIRECTORY external_student AS 'D:\';

最後建立外部表格：

例3：

CREATE TABLE tbl_external_student(       sid         NUMBER ,       sname   VARCHAR2(10),       sclass    VARCHAR2(3),       ssubject VARCHAR2(12),       sscore    NUMBER) ORGANIZATION EXTERNAL (               TYPE oracle_loader               DEFAULT DIRECTORY external_student               ACCESS PARAMETERS(FIELDS TERMINATED BY ',')               LOCATION ('student.csv')   ) 

註：外部的D盤下的檔案student.csv如下所示：

10001,siege,304,physics,80

查詢tbl_external_student與上述顯示一致。

分區表

在大型資料庫應用中，需要處理的資料量甚至可以達到TB級。為了提高讀寫和查詢速度，Oracle提供了一種分區技術，使用者可以在建立表時應用分區技術，將資料以分區形式儲存。

分區是指將表或索引分隔成相對較小的、可獨立管理的部分。分區後的表與未分區的表在執行DML語句時沒有任何區別。

對錶進行分區，必須為表中的每一條記錄指定所屬分區。一條記錄屬於哪一個分區是由分區表對該記錄的匹配欄位決定的。分區欄位可以是表中的一個欄位或者多個欄位的組合，在建立分區表時決定的。當使用者對分區表進行插入、更新或者刪除時，Oracle會自動根據分區欄位的值來選擇儲存的分區。

Oracle資料庫提供了5種對錶或索引的分區方法：定界分割、散列分區、列表分區、組合範圍散列分區和組合方位列表分區。

定界分割

定界分割就是對資料表中某個值的範圍進行分區，例如根據值的大小進行分區。建立定界分割，需要使用PARTITION BY RANGE子句。

如果不指定分區名，Oracle將自動對分區進行命名。

例4：

CREATE TABLE tbl_range_partition(       id NUMBER PRIMARY KEY,       name VARCHAR2(10),       subject VARCHAR2(8),       score NUMBER)PARTITION BY RANGE(score)(          PARTITION part1 VALUES LESS THAN(60) TABLESPACE learning,          PARTITION part2 VALUES LESS THAN(80) TABLESPACE learning,          PARTITION part3 VALUES LESS THAN(MAXVALUE) TABLESPACE learning);

註：只有企業版資料庫才支援分區

查詢時可按照分區進行查詢：

SELECT * FROM tbl_range_partition PARTITION(part1);

散列分區

通過HASH演算法均勻分布資料的一種分區類型，通過在I/O裝置上進行散列分區，可以使得分區的大小一致。建立散列分區需要使用PARTITION BY HASH子句。

例5：

CREATE TABLE tbl_hash_partition(       bid NUMBER(4),       bookname VARCHAR2(10),       bookprice NUMBER(4,2),       booktime DATE)PARTITION BY HASH(bid)(          PARTITION part1 TABLESPACE learning,          PARTITION part2 TABLESPACE learning);

查詢時可按照分區進行查詢：

SELECT * FROM tbl_hash_partition PARTITION(part1);

列表分區

適用於分區列的值為非數字或者日期資料類型，並且分區列的取值範圍較少時使用。例如，成績表中的科目列取值較少，就可以應用列表分區。建立列表分區需要使用PARTITION BY LIST子句。

進行列表分區時，需要為每一個分區指定一個取值列表，分區列的取值處於同一個列表中的行將被儲存在同一個分區。

例5：

CREATE TABLE tbl_list_partition(       bid NUMBER(4),       bookname VARCHAR2(10),       bookpress VARCHAR2(30),       booktime DATE)PARTITION BY LIST(bid)(          PARTITION part1 VALUES ('A出版社') TABLESPACE learning,          PARTITION part2 VALUES ('B出版社') TABLESPACE learning);

查詢時可按照分區進行查詢：

SELECT * FROM tbl_list_partition PARTITION(part1);

管理分區表 增加分區

為分區增加分區，需要使用ALTER TABLE … ADD PARTITION語句。增加分區主要分為以下幾種情況：

• 為定界分割表增加分區

  又可以分為兩種情況：在最後一個分區之後增加分區和在分區中間或開始處增加分區。

  例6：

  ALTER TABLE tbl_range_partition ADD PARTITION part3 VALUES LESS THAN(150);

  例7：

  ALTER TABLE tbl_range_partition SPLIT PARTITION part2 AT(70) INTO(PARTITION part6,PARTITION part7);

• 為散列分區表增加分區

  只需要使用ALTER TABLE ADD PARTITION語句即可，Oracle會自動在已有分區和建立分區之間進行容量均衡。

  例8：

  ALTER TABLE tbl_hash_partition ADD PARTITION part3;

• 為列表分區增加分區

  為列表分區表新增加一個分區，和建立列表分區時一樣需要為分區表使用VALUES子句指定取值列表。

  例9：

  ALTER TABLE tbl_list_partition ADD PARTITION part3 VALUES(default); 

  合并分區

  合并分區，需要使用ALTER TABLE … MERGE PARTITION語句。 例如，將前面建立的tbl_range_partition 表的part6和part7分區合并起來，如下：

  例10：

  ALTER TABLE tbl_range_partition MERGE PARTITIONS part6,part7 INTO PARTITION part2;

  刪除分區

  刪除分區，需要使用ALTER TABLE … DROPPARTITION語句。 例如，將前面建立的tbl_range_partition 表的part3分區刪除，如下：

  例11：

  ALTER TABLE tbl_range_partition DROP PARTITION part3;