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;