第04章 数据库对象
务级删除)。
(2)ON COMMIT PRESERVE ROWS：创建临时表后，插入数据的事务结束后保留数据，该会话结束时自动删除数据(会话级删除)。
(3)临时表的定义(结构)对于所有会话都是可见的，但是数据只有插入数据的会话才是可见的，因为数据在会话或事务结束后删除了。
(4)临时表有很多限制：不能被分区、索引组织、分簇；不能指定外键进行引用完整性约束；不能指定表空间、存储参数等。
例5：建立临时表
CREATE GLOBAL TEMPORARY TABLE student_temp
AS SELECT * FROM student ON COMMIT PRESERVE ROWS; 3.建立索引组织表
索引组织表(IOT:Index Organization Table)。一般情况下，表和索引是分别使用表段和索引段分开存储的(详见索引一节)。但索引组织表是将表的行和索引数据存储在一起的，将表的数据行作为B树索引叶节点。
普通表的索引查询分2步：(1)在索引中根据索引值查询相应数据行的rowid；(2)根据rowid在数据表中读取相应的行。而索引组织表在索引中，根据主码找到相应的数据行，减去了根据rowid映射数据行的过程，加快了查询速度，提高了性能。
如果某个表的大部分查询是根据主码进行的，建议建立索引组织表。索引组织表的建立要使用ORGANIZATION INDEX关键字，并指定表的主键。索引组织表一般存放在用户表空间，而不是存放在索引表空间。
例6：建立索引组织表 CREATE TABLE teacher(
teacher_id NUMBER(20) PROMARY KEY, name dno
VARCHAR2(10), VARCHAR2(10)
- 11 -
第04章 数据库对象
)
ORGANIZATION INDEX TABLESPACE users;
索引组织表对于主键的查询会显著提高查询速度，但是如果是对非主键的查询，反而没有普通表的查询速度，为了改善对非主键的查询速度，采用“溢出”存储功能，将非主码列存储在溢出区，而不是B树的叶节点上。对于大型的索引组织表，采用溢出存储功能，一是可以减少索引组织表占用的存储空间；二是加速了非主键列的查询速度。
如果采用溢出存储功能，要使用OVERFLOW子句，同时指定溢出的条件和溢出的字段。其中PCTTHRESHOLD指定索引叶节点中应该保留的空间百分比，当存储主键和部分非主键后，如果剩余空间低于PCTTHRESHOLD，则将用INCLUDING指定的列及其以后的列保存在溢出区。
例7：建立使用溢出存储的索引组织表 CREATE TABLE teacher(
teacher_id NUMBER(20) PROMARY KEY, name dno )
ORGANIZATION INDEX
OVERFLOW PCTTHRESHOLD 20 INCLUDING name TABLESPACE users; 4.建立分区表
对于数据量到达上百吉字节(GB)甚至太字节(TB)的巨型表，如人口普查表、移动用户表、话费原始记录表等等。这种巨型表有以下弊端：
(1)安全性：如果某个数据块产生错误，整个表将不可用，这对于巨型表来说是不安全的。 (2)查询速度慢：巨型表的查询需要执行大量的I/O操作，对系统性能有很大的影响。特别是一块硬盘不足以存放一个巨型表时，则一个数据段跨越多个磁盘的多个数据文件，会显著降低系统性能。
- 12 -
VARCHAR2(10), VARCHAR2(10)
第04章 数据库对象
对于这种巨型表，Oracle提供了分区技术。将一个巨型表进行划分，分别存放在较小的分区(partition)中，当进行查询时，可以只访问某个分区，而不必访问整个巨型表，从而加快了查询速度，提高了系统性能。分区与分表不同，如求平均、求和等操作需要访问整个表时，直接使用巨型表表名，不需要对各个分区进行操作。
Oracle提供了4中分区的方法：范围分区、列表分区、散列分区、组合分区。这里介绍常用的范围分区和列表分区。
(1)范围分区
范围分区使用PARTITION BY RANGE(column)关键字建立分区，日期性字段是常用分区字段。如销售表按季度分成4个区。使用关键字PARTITION 指明分区名。
例8：范围分区表 CREATE TABLE sales(
order_id
NUMBER(10), NUMBER(10), NUMBER(10), NUMBER(10), DATE,
customer_id goods_id
goods_amount sale_date
sale_province VARCHAR2(20) )
PARTITION BY RANGE(sale_date)(
PARTITION p1 VALUES LESS THAN(’2007-03-01’), PARTITION p2 VALUES LESS THAN(’2007-06-01’), PARTITION p3 VALUES LESS THAN(’2007-09-01’), PARTITION p4 VALUES LESS THAN(’2007-12-01’) );
说明：
①p1、p2、p3、p4分别为分区名，使用”表名.分区名”来标识分区，不同的分区可以使用
- 13 -
第04章 数据库对象
TABLESPACE，以便将个分区存放在不同的表空间。
②对于INSERT操作，根据插入数据，分别存放在不同的分区中，对于SELECT、UPDATE、DELETE操作，如果在WHERE条件中引用了分区列，Oracle将会在相应的分区上进行操作。
③指定分区列时，要能尽量将记录较均匀地分布在各个分区中，如果全部挤在一个分区，就失去了分区的意义。
(2)列表分区
如果分区列的值不能进行范围划分(不是数值或日期)，而且分区字段值在一个较小的集合内，可以按列表分区。如区进行分区的销售表：
例8：范围分区表 CREATE TABLE sales(
order_id
NUMBER(10), NUMBER(10), NUMBER(10), NUMBER(10), DATE,
customer_id goods_id
goods_amount sale_date
sale_province VARCHAR2(20) )
PARTITION BY LIST(sale_province)(
PARTITION p1 VALUES(’辽宁’,’吉林’,’黑龙江’),
PARTITION p2 VALUES(’北京’,’天津’,’河北’,’山西’,’内蒙’),
PARTITION p3 VALUES(’上海’,’江苏’,’浙江’,’安徽’,’福建’,’江西’,’山东’), PARTITION p4 VALUES(’河南’,’湖北’,’湖南’,’广东’,’广西’,’海南’) PARTITION p5 VALUES(’重庆’,’四川’,’贵州’,’云南’,’西藏’) PARTITION p6 VALUES(’陕西’,’甘肃’,’青海’,’宁夏’,’新疆’) PARTITION p7 VALUES(’河南’,’湖北’,’湖南’,’广东’,’广西’,’海南’,) PARTITION p8 VALUES(’香港’,’澳门’,’台湾’)
- 14 -
第04章 数据库对象
);
说明：
①列表分区用LIST关键字。使用VALUES直接列出分区的值，不是范围的比较。 ②所有分区中的列表值，要包括全部的值；在输入时，要与列表值一致。
4.1.5 修改表
表建立后，要对表进行一些修改，以便适应需求。维护主要有： 增加列或限制：ALTER TABLE tablename ADD 删除列或限制：ALTER TABLE tablename DROP 修改列或限制：ALTER TABLE tablename MODIFY (1)增加列：
SQL>ALTER TABLE teacher ADD address VARCHAR2(50); 说明：对现有记录，新增列值为NULL或设置缺省值。 (2)增加限制：
SQL>ALTER TABLE teacher ADD
CONSTRAINT teacher_sex_ck CHECK(Sex in (‘男’,’女’));
(3)删除限制：
SQL>ALTER TABLE teacher
DROP CONSTRAINT teacher_id_pk CASCADE;
说明：CASCADE删除主键和唯一键的同时删除外键。 (4)删除列：
SQL>ALTER TABLE teacher DROP COLUMN sex;
//删除单列用COLUMN
SQL>ALTER TABLE teacher DROP (sex,address); //删除多列 SQL>ALTER TABLE teacher SET UNUSED (sex); //设置列无效 SQL>ALTER TABLE teacher DROP UNUSED COLUMNS;//删除无效列 (5)修改表名:
SQL>ALTER TABLE teacher RENAME TO teacher_info;
- 15 -