Oracle表的创建与管理二（创建临时表和分区表）

一.创建临时表

1、临时表概述

临时表创建后，其结构将一直存在，但其数据只在当前事务或当前会话中有效。
事务级临时表(ON COMMIT DELETE  ROWS)在事务提交时系统自动删除表中的所有记录。
会话级临时表(ON COMMIT PRESERVE  ROWS 在会话终止时系统自动删除表中的所有记录。

2、语法格式

CREATE  GLOBAL TEMPORARY TABLE [schema_name.]temp_tablename
([column_name data_type
     [DEFAULT expr][PRIMARY KEY] [NULL|NOT NULL] ]
[ , …])
[ON COMMIT DELETE ROWS]      --默认为事务级别临时表
[ON COMMIT PRESERVE ROWS]   --会话级别临时表
;

 临时表的作用：对于庞大的数据我们只要查询其中一小部分结果集这样我们可以借助临时表。

二.创建分区表

1、分区的概念

分区是指将一个巨型表或巨型索引分成若干个独立的组成部分进行存储和管理，每个相对小的可以独立管理的部分称为原来表或索引的分区。

对表进行分区的情况：
表的大小超过2GB，且表中不含有LONG或LONG RAW类型的列。
对一个表做并行的DML操作时，应对表进行分区。
为平衡I/O操作，需将一个表存储在不同表空间时，必须对表进行分区。
需将表一部分设置为只读，另一部分设置为可更新时，必须对表进行分区。

2、分区的类型

1)范围分区

范围分区是按照分区列值的范围来对表进行分区的。一般用于数值类型和日期类型。
语法格式：

CREATE  TABLE [schema_name.] table_name
([column_name data_type
     [DEFAULT expr][PRIMARY KEY] [NULL|NOT NULL] ]
[ , …])
PARTITION BY RANGE(column_name)
( PARTITION pn VALUES LESS THAN (values|MAXVALUE) TABLESPACE  tablespace_name      [,]);

 [示例1]创建范围分区表
创建一个分区表student_range，将学生信息根据其出生日期进行分区，将1980-1-1前出生的学生信息保存在ORCLTB1表空间中，将1980-1-1至1990-1-1间出生的学生信息保存在ORCLTB2表空间中，其他的学生信息保存在USERS表空间。

CREATE TABLE student_range
( sno number(6) PRIMARY KEY,
 sname varchar2(20),
 birth date)
PARTITION BY RANGE(birth)
( PARTITION p1 VALUES LESS THAN(to_date(‘1980-1-1','yyyy-mm-dd')) TABLESPACE orcltbs1,
   PARTITION p2 VALUES LESS THAN(to_date(‘1990-1-1','yyyy-mm-dd')) TABLESPACE orcltbs2,
   PARTITION p3 VALUES LESS THAN(maxvalue) TABLESPACE users
);

2)列表分区

如果分区列的取值是非数值型数据，且分区列的取值范围只是少数值的有限集合，则可对表采用列表分区。
语法格式：

CREATE  TABLE [schema_name.] table_name
([column_name data_type
     [DEFAULT expr][PRIM9ARY KEY] [NULL|NOT NULL] ]
[ , …])
PARTITION BY LIST(column_name)
( PARTITION pn VALUES  (values)
     TABLESPACE  tablespace_name      [,])

[示例2 ]：创建列表分区表
创建一个分区表student_list，将学生信息根据性别进行分区，将女生信息保存在ORCLTB1表空间中，将男生信息保存在ORCLTB2表空间中。

CREATE TABLE  student_list
( sno number(6) PRIMARY KEY,
  sname varchar2(20),
  sex char(2),
  birth date)
PARTITION BY LIST(sex)
( PARTITION p1 VALUES (‘女’) TABLESPACE orcltbs1,
   PARTITION p2 VALUES (‘男') TABLESPACE orcltbs2
);

3)散列分区

如果需要在各分区中均衡分配数据，则可对表采用散列(Hash)分区。语法格式：

CREATE  TABLE [schema_name.] table_name
([column_name data_type
     [DEFAULT expr][PRIMARY KEY] [NULL|NOT NULL] ]
[ , …])
PARTITION BY HASH(column_name)
[( PARTITION pn TABLESPACE  tablespace_name   [,])]
[PARTITIONS n STORE IN(tbsp_name1, tbsp_nameN)]

 [示例3 ]：创建HASH分区表
创建一个分区表student_hash，将学生信息均衡保存在ORCLTB1和ORCLTB2表空间中。

CREATE TABLE  student_hash
( sno number(6) PRIMARY KEY,
  sname varchar2(20),
  sex char(2),
  birth date)
PARTITION BY hash(sno)
( PARTITION p1 TABLESPACE orcltbs1,
   PARTITION p2 TABLESPACE orcltbs2);

4)复合分区

结合两种基本的分区方法，先采用一个分区方法对表或索引进行分区，然后再采用另一个方法将分区再划分若干个子分区，每个分区的子分区都是数据的一个逻辑子集。需指明分区方法，分区列，子分区方法，子分区列，各子分区描述等。
 范围-列表复合分区   范围-散列复合分区   范围-范围复合分区
 列表-范围复合分区   列表-散列复合分区   列表-列表复合分区

3、管理分区表

1)查询分区表的字典
dba_part_tables
all_part_tables
user_part_tables
2)显示表分区的数据字典
dba_tab_partitions
all_tab_partitions
user_tab_partitions