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;