Oracle编程入门经典第7章表

2013-02-24 20:57 夜雨瞳阅读(875) 评论(0) 编辑收藏举报

表是在行和列中存储数据的基本结构。而且，就如同Oracle向数据库整体增加特性一样，随着时间失衡，它也增强了表的概念，以适应更加复杂的应用要求。在本章中，我们将要讨论：

Oracle中最常用的表类型，以及用户为什么要使用各种类型。
用户在Oracle中将会遇到的表特性，它们可以影响表的操作方式。
怎样ALTER、DROP和TRUNCATE表

7.1 介绍Oracle中的表

在Oracle中，存储数据从没有如此容易或如此高效。除了对SQL优化器进行了改进外，数据库内核、数据库管理配置选项等也都得到了强化。Oracle已经发布了新类型的表，来适应各种类型的数据存储、数据访问以及性能要求。

对于所有类型的表，Oracle都允许开发人员和管理员规定各种表属性，它们会确定如下内容：

哪一个表空间包含表
Oracle怎样将表物理存储在磁盘上
当从磁盘读取表数据的时候，Oracle怎样将它们与内存映射
Oracle怎样控制表上特定操作的日志

新的表类型还能够在开发和管理解决方案的时候节省用户时间。随着用户掌握不同类型的可用表，用户将会发现这些表可以满足那些使用堆存储的标准表之外的要求。这些要求过去通常要由处理问题的开发人员/或者管理员使用其它方式解决，但是因为Oracle的新表类型将会处理这些问题，所以就可以省去这些工作。

7.2 表类型

7.2.1 堆表

最基本的表类型就是堆表（Heap table）。术语堆是指数据在磁盘上随机存储的方式。一般来说，Oracle在将行写入数据块的时候不会考虑其它的行的存储位置。当向堆表插入行的时候，数据库会将数据写入第一个具有足够自由空间的段。当更新和删除行的时候，就会为新的插入提供可用空间。

为了展示堆表，我们将要模型化一个主要涉及信息技术领域的公司的培训和教育部门。

试验：建立堆组织的表

(1) 我们将要建立的第一个是SUBJECTS，它给出了培训部门将要教授的课程类别。

SQL> create table subjects(
  2  subject_id number not null,
  3  subject_name varchar2(30) not null,
  4  description varchar2(4000)
  5  )
  6  tablespace users
  7  /
表已创建。

就如用户所见，我们已经建立了3列，名称分别为SUBJECT_ID、SUBJECT_NAME、DESCRIPTION。这些列分别具有数据类型NUMBER、VARCHAR2(30)和VARCHAR2(4000)。注意，要使用NOT NULL子句来确保SUBJECT_ID和SUBJECT_ANME具有值。

(2) 现在已经建立SUBJECTS表，我们将要使用ALTER TABLE命令，使SUBJECT_ID列成为主键：

SQL> alter table subjects
  2  add constraint pk_subjects
  3  primary key(subject_id)
  4  /
表已更改。

(3) 建立了SUBJECTS表之后，我们将会继续建立一个COURSES的子表，它将会存储SUBJECTS表中各个学科的课程。

SQL> create table courses(
  2  course_id number not null,
  3  course_name varchar2(60) not null,
  4  subject_id number not null,
  5  duration number(2),
  6  skill_lvl varchar2(12) not null
  7  )
  8  tablespace users
  9  /
表已创建。

在这个例子中，我们已经建立了COURSES列的数据类型。

(4) 现在我们需要定义约束。

SQL> alter table courses
  2  add constraint pk_courses
  3  primary key(course_id)
  4  /
表已更改。

其次，我们希望COURSES的SUBJECT_ID列能够成为引用SUBJECTS表的外键：

SQL> alter table courses
  2  add constraint fk_course_subj
  3  foreign key(subject_id) references subjects(subject_id)
  4  /
表已更改。

最后，我们想要在SKILL_LVL列上实现一个检查约束，以确保各行中这个列的唯一可能值是BEGINNER、INTERMEDIATE或ADVANCED；

SQL> alter table courses
  2  add constraint ck_level check(
  3  skill_lvl in('BEGINNER','INTERMEDIATE','ADVANCED')
  4  )
  5  /
表已更改。

工作原理

这里向用户提供了建立2个彼此相关联的堆组织表的示例。我们使用TABLESPACE子句来确保将表放到正确的表空间中，并且使用约束来强制一些与表中存储数据相关联的业务规则。

7.2.2 外部表

在Oracle 9i中新出现的外部表（external tables）是在数据库以外的文件系统上存储的只读表。

在Oracle 9i以前，使用操作系统上的普通文件中存储数据的唯一方式就是通过SQL*Loader工具将其载入数据库，或者相应于普通文件中的数据使用INSERT，手工建立堆组织表。

通过使用外部表，就无须将数据复制到数据库中，并且强制更新，我们可以让数据保留在普通文件中，并且允许数据库对其进行实地读取。这种方法，外部应用可以采用它认为合适的方法更新数据，而且也不用调用SQL*Loader执行数据载入操作。

试验：建立和使用外部表

在以下的示例中，我们将要根据包含有逗号分隔值的文件文件（teacher.csv,通过建立Excel表格另存为teacher.csv，并放在C:\），建立一个示例外部表。

(1) 为了建立外部表，Oracle需要知道文件在操作系统上的位置。这可以使用目录对象来实现，这个数据库对象可以作为服务器文件系统上目录的别名。为了建立指向我们数据文件位置的目录，用户需要CREATE ANY DIRECTORY特权，这意味着要使用DBA账号，或者要为用户用于这个示例的用户账号赋予这个权限。

（具体赋予权限语句为grant create any directory to scott with admin option;）

SQL> create directory ext_data_files
  2  as 'C:\'
  3  /
目录已创建。

用户需要根据自己放置teacher.csv文件的位置修改这个路径，这里是把它放在C:\

(2) 现在，我们要为外部表建立表定义。Oracle所存储的唯一信息就是外部表的元数据。数据库要负责在提交对外部表的查询的时候从外部数据源中获取数据：

SQL> create table teachers_ext(
  2  first_name varchar2(15),
  3  last_name varchar2(15),
  4  phone_number varchar2(12)
  5  )
  6  organization external(
  7  type oracle_loader
  8  default directory ext_data_files
  9  access parameters(
 10   fields terminated by ',')
 11  location('teacher.csv')
 12  )
 13  reject limit unlimited
 14  /
表已创建。

(3) 既然已经建立了表，那么我们就可以在其上执行SQL查询，从Oracle数据库请求操作系统上存储的文件的信息：

SQL> select first_name,last_name,phone_number
  2  from teachers_ext;
 
FIRST_NAME      LAST_NAME       PHONE_NUMBER
--------------- --------------- ------------
Jean            Miller          123-0107
Jean            Miller          123-0108

如果用户打开数据文件，用户就会发现2条记录。用户可以注意到，这个文件没有发现任何变化或者改动。Oracle只是会根据外部表TEACHERS_EXT的定义原样读取文件，让数据库可以使用数据。

以上是TEACHERS_EXT表的操作日志。

工作原理

CREATE TABLE 语句要比标准的堆组织表更复杂一些，所以我们将会逐步分析完整的DDL语句。

前5行与其它的CREATE TABLE 语句大体相同：

SQL> create table teachers_ext(
  2  first_name varchar2(15),
  3  last_name varchar2(15),
  4  phone_number varchar2(12)
  5  )

在将数据文件的内容与表中的列进行了映射之后，接下来，我们就要使用ORGANIZATION EXTERNAL子句指出正在建立的表的外部表。

Oracle为外部表主要提供了两种驱动

1. the loader access driver, or ORACLE_LOADER

2. the import/export access driver, or ORACLE_INTERNAL

  6  organization external(
  7  type oracle_loader

在CREATE TABLE语句的下一部分中，要使用DEFAULT_DIRECTORY和LOCATION属性规定外部文件在操作系统的文件系统上的位置：

  8  default directory ext_data_files
  9  access parameters(
 10   fields terminated by ',')
 11  location('teacher.csv')
 12  )

CREATE TABLE 语句的最后一个子句是REJECT LIMIT子句：

13  reject limit unlimited
14  /

这个子句会告诉Oracle在将源数据转换为表定义中映射的列数据类型期间，数据库允许多少错误。如果用户在执行一个查询，而Oracle遇到了超过这个转换数量的错误，那么查询就会失败。REJECT LIMIT的默认值是0。UNLIMITED关键词表明将会忽略转换错误，查询决不会失败。在最糟糕的情况下，如果外部数据文件中的所有记录都由于转换错误而失败，那么查询这个表就会只返回0行。

1 Type（或者访问驱动器类型）

外部表的访问驱动器（access driver）是一种工具，它可以将数据从它们最初的格式转换为可以向服务器提供的方式。换句话说，表的建立者/所有者能够使用访问驱动器发动源文件，以便它们能够被数据库读取。

Oracle附带了默认的访问驱动器ORACLE_LOADER，然而用户应该注意到，还可以构建访问驱动器去支持其它外部表类型。

2 DEFAULT DIRECTORY

当用户建立外部表的时候，用户实际只是在数据库中存储了元数据（一般认为，所谓元数据是关于数据的数据，或关于数据的结构化的数据）。数据本身还是在数据库之外存储。当定义外部表的时候，用户将要定义一个默认目录，告诉Oracle外部表文件位于文件系统的何处。

对于我们已经建立的默认目录EXT_DATA_FILES，我们可以进行如下所示授权：

SQL> grant all on directory ext_data_files to scott
  2  /
授权成功。

这可以让SCOTT有能力建立外部表，并且在表定义中使用EXT_DATA_FILES目录对象。

3 BADFILE和NOBADFILE

当读取用户的外部表时，数据库可能会遇到数据类型转换错误，不能够将源文件转换成数据库中为外部表定义的列。如果用户在CREATE TABLE语句中使用如下语法，那么所有不能转换的数据值都会写入BADFILE中：

SQL> create table teachers_ext(
  2  first_name varchar2(15),
  3  last_name varchar2(15),
  4  phone_number varchar2(12)
  5  )
  6  organization external(
  7  type oracle_loader
  8  default directory ext_data_files
  9  access parameters(
 10  records delimited by newline
 11  badfile ext_data_files:'teacher.bad'
 12  fields terminated by ',')
 13  location('teacher.csv')
 14  )
 15  reject limit unlimited
 16  /
表已创建。
SQL> select first_name,last_name,phone_number from teachers_ext;
FIRST_NAME      LAST_NAME       PHONE_NUMBER
--------------- --------------- ------------
Jean            Miller          123-0107
Jean            Miller          123-0108
Jean            Mille12r        123-0109

type oracle_loade-——指定外部表的访问方式

records delimited by newline——以新的一行分隔记录

badfile ext_data_files:'teacher.bad'——读取记录转换失败存取到.bad文件里

将第3条记录更改为：

Jean,Mill12341234321212e12r,123-0109

因为Mill12341234321212e12r太过长，所以只显示2条记录：

SQL> select first_name,last_name,phone_number from teachers_ext;
FIRST_NAME      LAST_NAME       PHONE_NUMBER
--------------- --------------- ------------
Jean            Miller          123-0107
Jean            Miller          123-0108

转换为元数据失败的记录存放在.bad文件中

BADFILE非常有用，因为它们可以为表的所有者提供一个可供分析的文件，找到发生错误的记录。对于在他们的应用中使用了外部表的管理员，使用BADFILE也会非常有助于调整源数据或者表定义，以确保源文件中的所有数据都可以由Oracle读取。

如果没有调整数据的原因，或者这些转换错误无关紧要，那么就可以在建立表的时候使用NOBADFILE子句。这将会让Oracle完全不理会数据类型转换错误，只是简单忽略错误记录。NOBADFILE可以在CREATE TABLE命令中规定，如下所示：

SQL> create table teachers_ext(
  2  first_name varchar2(15),
  3  last_name varchar2(15),
  4  phone_number varchar2(12)
  5  )
  6  organization external(
  7  type oracle_loader
  8  default directory ext_data_files
  9  access parameters(
 10   nobadfile
 11   fields terminated by ',')
 12  location('teacher.csv')
 13  )
 14  reject limit unlimited
 15  /
表已创建。

如果没有在CREATE TABLE语句中规定BADFILE和NOBADFILE，在默认情况下，Oracle就会使用表名称和.BAD扩展名为主，在数据文件所处的目录建立一个BADFILE，其名称和日志名相同。

4 LOGFILE和NOLOGFILE

LOGFILE可以用于写入在Oracle尝试访问数据文件的时候所遇到错误的消息。这在设置新的外部文件表的时候非常有用，因为在第一次建立表的时候，外部表中会经常发生错误。如果操作系统限制Oracle读取文件，或者用于实例的源数据文件没有存在，那么就会将这些错误记录到日志文件中。

当应该忽略访问外部数据源的时候所遇到的错误时，就要使用NOLOGFILE。如果规定了这个子句，Oracle就不会将错写入任何日志文件。

如果CREATE TABLE语句没有规定LOGFILE或者NOLOGFIEL，Oracle就会在默认情况下建立一个LOGFILE。LOGFILE的名称将会是<table name>.LOG.

简而言之，外部表是将外部数据文件手工载入数据库的绝佳替代方法。在Oracle 9i的第一次发布中，外部表还是吟诗的，只能够用于查询数据。Oracle没有提供内部的方式去更新或者删除这些表中的记录。另外，Oracle也不能在外部表上建立索引。这对于用户表的需求可能会有一些限制。

索引能力的缺失意味着SELECT语句中Oracle总是要完全搜索外部表。如果用户需要索引外部表，用户就要选择基于外部表的内容，建立标准表（或者索引组织表，如下所述）。任何可以对用户的标准表进行索引。用户可以使用Oracle的内部作业高度机制DBMS_JOB去管理外部表和标准表之间的引用刷新。

7.2.3 索引组织表

索引组织表（或者IOT）这种表结构可以存储索引这样的内容，以辅助查询性能。索引组织表会以牺牲插入和更新性能为代价提供极好的查询性能。假如要使用Webster辞典，搜索一些词的意义，那么当用户在辞典中搜索这个词的时候，用户就要将书打开到这个词的附近位置。用户可以根据词的字母次序知道在哪里找到这个词，然后再基于用户打开书的位置进行前后搜索。这就是B树的实际示例。这也是索引组织表的完美示例。

SQL> create table states(
  2  state_id varchar2(2),
  3  state_name varchar2(20),
  4  constraint states_pk
  5   primary key(state_id)
  6  )
  7  organization index
  8  /
表已创建。

在这个救命中，我们拥有一个简单的查找表，它由2个列构成：STATE_ID和STATE_NAME。这个表最常被访问的方式是基于STATE_ID列查找STATE_NAME。语句末尾的ORGANIZATION INDEX会告诉Oracle这个表是索引组织表。

在索引组织表中，会根据表的主键列进行大B树索引排序，将数据写入磁盘。这样能够在使用表的主键列在IOT上进行查询的时候得到更好的读取性能。

IOT要比堆组织表更难于维护。当向堆表中写入行的时候，Oracle会简单地使用表盘区中的第一个可用空间写入数据。然后对于IOT，当插入行的时候，它必须要将数据组织到它的“位置”。由于IOT要像索引一样组织，所以Oracle要根据所插入新行的主键将数据写入合适的数据块。当前在数据块中存储的行会进行移动，以容纳新写入数据块。在用户应用中使用SQL访问索引组织表与访问堆组织表没有区别。当用户在堆组织表上提交查询的时候，Oracle可以使用索引访问表。这样依赖于各种因素，例如（但是不限于）：

所使用的优化器（基于开销还是基于规则）
在查询的WHERE子句中规定的列
是否相应于查询的WHERE子句中的列，在表上建立了的索引

如果Oracle决定使用索引，就是因为所估计的开销（执行查询所需的资源）要小于执行全表搜索的开销。如果Oracle决定使用索引，它首先会咨询索引来判断它需要读取的数据块，然后再执行那些读取，获取实际的数据。例如，我们来观察Oracle怎样基于表的行数执行索引，首先，我们要建立一个小表：

SQL> create table t as
  2  select * from all_objects where rownum<51
  3  /
表已创建。

所建立的这个表具有50行，所以我们要在OBJECT_ID列上建立一个索引：

现在，我们要分析这个表，帮助Oracle SQL优化器确定在我们查询它的时候，是使用索引读取还是全表搜索：

SQL> analyze table t compute statistics
  2  /
表已分析。

我们现在可以使用SQL*Plus AUTOTRACE工具查看Oracle怎样查询数据：

（如果set autotrace on出现：

SP2-0613: 无法验证 PLAN_TABLE 格式或实体
SP2-0611: 启用EXPLAIN报告时出错

则参考“第6章在Oracle中的处理语句”，开启Oracle的跟踪调试）

SQL> set autotrace on

SQL> select * from t where object_id=10;

未选定行

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=89)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          89)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=
          1)

Statistics
----------------------------------------------------------
        990  recursive calls
          0  db block gets
        175  consistent gets
         11  physical reads
          0  redo size
        915  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL> set autotrace off

当我基于这个索引中的列执行查询时，用户可能会认为Oracle会选择执行索引读取。然而，为了在这个表上执行索引读取，我们至少要读取一个索引块和一个表数据块。在这个例子中，表的完全执行将会需要更少的读取。

我们现在来向表t增加更多的记录：

SQL> drop table t
  2  /
表已丢弃。

SQL> create table t as select * from all_objects where rownum<10001
  2  /
表已创建。
 
SQL> create index t_idx on t(object_id);
索引已创建。

SQL> analyze table t compute statistics;
表已分析。

SQL> set autotrace traceonly
SQL> select * from t where object_id=10
  2  /
未选定行

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=83)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=2 Card=1 Bytes=
          83)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=
          1)

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          6  consistent gets
          4  physical reads
          0  redo size
        915  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed

现在，由于这里有了大量的行，所以用户可以从AUTOTRACE命令的结果中看到，Oracle会选择执行索引读取，而不是全表搜索。

对于总是要通过特定索引访问的表（具有不频繁的更新或者插入），使用索引组织表来代替堆组织表可以提高性能。这是因为Oracle不必再决定是否要使用索引作为表的访问路径。另外，由于不需要同时存储索引数据和表数据，Oracle的存储要求也会最小。

索引组织表还有三个特性，我们现在来讨论它们。

1 COMPRESS和NOCOMPRESS

压缩实际上是一个可以用于所有索引的选项，而不仅仅是IOT，它是指索引中的数据在数据块中实际存储的方式。COMPRESS属性中规定的值（整数）直接对应于索引组织表的主键中不应该多次存储的列数量。开始的列只要存储一次，而随后的表项只需存储其余与最初表项不同的列。在以下的示例中，我们将要建立2个索引组织表，一个使用了压缩，而另一个没有，以展示这个内容：

SQL> connect hr/hr;
已连接。

SQL> create table locations_iot(
  2   region_id,country_id,location_id,
  3   primary key(region_id,country_id,location_id)
  4  )
  5  organization index
  6  nocompress
  7  as select c.region_id,l.country_id,l.location_id
  8   from locations l,countries c
  9   where l.country_id=c.country_id
 10  /
表已创建。

SQL> create table locations_iot_c(
  2   region_id,country_id,location_id,
  3   primary key(region_id,country_id,location_id)
  4  )
  5  organization index
  6  compress 2
  7  as select c.region_id,l.country_id,l.location_id
  8   from locations l,countries c
  9   where l.country_id=c.country_id
 10  /
表已创建。

这2个表都使用了几乎相同的CREATE TABLE语句，利用了相同的数据来构建。唯一的区别就是2个语句的第7行的COMPRESS/NOCOMPRESS属性。以下的表描述了数据在磁盘上的数据块中的存储方式。

表7-1 描绘了使用NOCOMPRESS设置作为压缩属性的LOCATIONS_IOT表。

表7-1 使用NOCOMPRESS设置的数据存储

1，CH，2900	1，CH，3000	1，DE，2700	1，IT，1000	1，IT，1100
1，NL，3100	1，UK，2400	1，UK，2500	1，UK，2600	2，BR，2800
2，CA，1800	2，CA，1900	2，MX，3200	2，US，1400	2，US，1500
2，US，1600	2，US，1700	3，AU，2200	3，CN，2000	3，IN，2100
3，JP，1200	3，JP，1300	3，SG，2300

与此相似，在使用COMPRESS 2 设置作为压缩属性的LOCATIONS_IOT_C表中，见表7-2。

表7-2 使用COMPRESS 2设置的数据存储

1，CH，2900	3000	1，DE，2700	1，IT，1000	1100
1，NL，3100	1，UK，2400	2500	2600	2，BR，2800
2，CA，1800	1900	2，MX，3200	2，US，1400	1500
1600	1700	3，AU，2200	3，CN，2000	3，IN，2100
3，JP，1200	3，JP，1300	3，SG，2300

在LOCATIONS_IOT表中，因为在表中没有压缩，所以每个数据块表项都有所有的三个值。然而，在LOCATIONS_IOT_C表中，有的数据块表项要比其余的小。例如，第一个数据块表是1，CH，2900。而第二个表项是3000。这是因为第二个表项开始的2个列与第一个表项相同，不需要再进行重复。相同的压缩也可以为如下REGION_ID/COUNTRY_ID组合实现：

CH
IT
UK
CA
2，US

这样就可以使存储在数据块中的数据数量更少。

2 OVERFLOW

通常，索引要表示表中的一个关键列（或者最多最少量的列）。一般情况下，不能够在索引中存储类似于大规模的VARCHAR2列或者LOB这样的大型列。所以B树索引将会采用小型的紧密聚焦的数据块。然而，在索引组织表中，各个行的大小可能会非常大，将这样的数据存储成索引将会降低所获得的性能。OVERFLOW是为这种类型的表提供的机制，它可以将经常要查询的数据放在基本索引块中，而将不经常查询（或者较大的数据列）存储在另外的段中，这种段称为溢出段。有2个选项可以用来规定怎样将数据存储在，或者移植到溢出段中：INCLUDING和PCTTHRESHOLD。

3 INCLUDING

当使用INCLUDING子句的时候，管理员可以规定表中的一个列，它会将列划分为在表的常规数据段中存储的列（包括主键列），和在溢出段中存储的列（包括已列出的那个列）。

我们可以使用以上的LOCATIONS_IOT表，建立一个索引组织表，它会包含HR模式LOCATIONS表的列。关键列将会存储在表的数据段中，而所有其它列将会存储在溢出段中：

SQL>  create table locations_inc(
  2     region_id,country_id,location_id,street_address,postal_code,city,state_province,
  3     primary key(region_id,country_id,location_id)
  4    )
  5    organization index
  6   nocompress
  7   overflow
  8   including street_address
  9  as select c.region_id,l.country_id,l.location_id,l.street_address,l.postal_code,l.city,l.state_
province
 10    from locations l,countries c
 11    where l.country_id=c.country_id
 12  /
表已创建。

在这个例子中，作为索引组织表建立了名为LOCATIONS_INC的表。生成这个表的行是从LOCATIONS和COUNTRIES表中抽取的（如CREATE TABLE语句的AS SELECT子句所示）。LOCATION_INC表将会存储在2个不同的段中，主段将会包含REGION_ID、COUNTRY_ID和LOCATION_ID列。第2个段，也就是溢出段，将会STREET_ADDRESS、POSTAL_CODE、CITY和STATE_PROVINCE列。

4 PCTTHRESHOLD

一行中的所有数据都必须与由PCTTHRESHOLD值标识的数据块百分比相匹配。任何大于这个尺寸的行都要分隔到2个存储位置：

关键列存储在表段中
所有其它列存储在溢出段中

当用户拥有变化长度的数据，没有办法判断各行大小时，PCTTHRESHOLD就会很有效。通过将大量的数据存储在溢出段中，访问关键列的查询性能就会提高，这是因为关键列会存储在主表段中，这些查询可以避免访问更大的溢出段。

简而言之，使用索引组织表的最好特性之一就是用户不必改变用户代码就可以使用它们。如果只考虑SQL，那么索引组织表与其它的表完全相同，所以，如果用户发现用户应用中要经常读取一个表，而且查询大多数集中在主键列上，那么用户就可以将这个表重新建立为索引组织表，进而获得益处。然而，我们也注意到了它们的使用具有一些特点，只有通过试验，并且在用户应用中测试它们，用户才能够了解它们什么时候对用户有用。

7.2.4 临时表

Oracle的临时表是那些只在事务处理或者会话进行期间存在数据的表。数据会在事务处理或者会话开始以后插入临时表，当事务处理或者会话完成之后就会删除。通过采用这种方式，开发者就可以在它们希望执行的应用逻辑（例如存储PL/SQL代码）的运行期间访问临时存储区域。

注意：

临时表完全不同于堆表。向堆表中插入数据非常缺乏效率，只有当事务处理结束时才可以将其删除。

Oracle的临时表与大多数其它关系数据库供应商的临时表的工作方式有所不同。用户不需要在每次希望使用临时存储的时候都建立临时表。与此相反，作为用户应用开发的组成部分，就如同用户建立常规应用表一样，用户只需建立一次临时表，而且只需一次。通过使用这种方式，每当在用户应用中使用临时表存储行的时候，就免除了重新建立表的负担。因此，用户可以向处理其它表一样，使用相似的命名惯例，在开发过程中指定表的名称。

相对于堆表，因为其它的用户决不会使用这些记录，所以临时表不需要在它们包含的记录上维护锁定。与此同时，它们也不必维护过多的重做日志信息来防止数据库故障，用户不会希望从事务处理或者会话的中间继续应用逻辑。

直到用户实际向表中插入数据的时候，才会为临时表分配空间，而与此相对的堆表会在CREATE TABLE 语句执行之后就分配一个区域。不仅如此，而且为存储用户数据分配的空间还来自于它们的临时表空间，而不是与永久对象的数据存储争用表空间。

尽管用于临时表的数据存储机制与用于标准堆表的机制有明显的差异，但是2种类型还具有一些相似性：

用户能够截取特定于用户会话的临时表（TRUNCATE TABLE命令将会在本章后面解释）
用户可以在临时表上建立索引
用户可以在临时表上建立视图
用户可以在临时表上建立触发器（因为用户不能够在临时表上建立外键，所以触发器能够用来辅助参考一致性）

应该在需要临时存储数据的应用中使用临时表。以下的行为属性列表展示了Oracle处理临时表，提高应用性能的方式：

不为临时表建立重做日志
直到在临时表上使用了第一个INSERT语句之后才分配数据段
TRUNCATE TABLE命令只会为发出这个命令的会话在表中截取数据。使用这个表的其它会话的数据不会受到影响
对于事务处理和会话范围，对待临时表上索引的方式与对待临时表的方式相同
因为没有2个会话或者事务处理能够操作相同的行，所以在临时表上不要求DML锁定

试验：建立临时表

在这部分中，我们将要建立2个临时表，一个针对事务处理，一个针对会话。这个试验要展示2种临时表类型之间的区别，让用户理解怎样建立它们。

(1) 首先，我们要建立2个表（要注意：GLOBAL TEMPORARY TABLE中的GLOBAL是标准，没有其它类型的临时表）

SQL> create global temporary table session_tab
  2  on commit preserve rows
  3  as select * from employees
  4  /
表已创建。
 
SQL> select count(*) from session_tab;
  COUNT(*)
----------
       107

SQL> create global temporary table transaction_tab
  2  on commit delete rows
  3  as select * from employees
  4  where 1=0
  5  /
表已创建。
 
SQL> insert into transaction_tab
  2  select * from employees
  3  /
已创建107行。
 
SQL> select count(*) from transaction_tab;
  COUNT(*)
----------
       107

现在，我们来使用COMMIT命令，分析其影响

SQL> commit;
提交完成。

SQL> select count(*) from session_tab;
  COUNT(*)
----------
       107

SQL> select count(*) from transaction_tab;
  COUNT(*)
----------
         0

就如用户所见，经过COMMIT后，SESSION_TAB表中的记录会得到保留，而TRANSACTION_TABL中的记录会被删除。

(2) 接下来，我们将要断开会话，重新连接相同的用户，查看重新启动会话对表的影响。

SQL> disconnect;
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

SQL> connect hr/hr;
已连接。

SQL> select count(*) from session_tab;
  COUNT(*)
----------
         0

SQL> select count(*) from transaction_tab;
  COUNT(*)
----------
         0

工作原理

ON COMMIT PRESERVE ROWS子句规定了对于各次提交，所有的行都应该原样保留在表中。由于会话可以包括许多事务，而临时表中的记录可以跨事务处理保存在表中，所以PRESERVE ROWS就可以规定一个专用于会话的临时表。

ON COMMIT DELETE ROWS规定了针对各次提交，临时表中的所有行都会被删除。由于提交会界定事务处理，所以DELETE ROWS可以规定专用于事务处理的临时表。

用户还可以注意到，当建立TRANSACTION_TAB表的时候，我们使用了子句：

WHERE clause of 1=0

这样就可以避免在CREATE GLOBAL TEMPORARY TABLE语句执行之后，在CREATE语句中插入到表中的行都被删除。因为作为规则，在DDL语句执行前后在后台都会使用COMMIT语句（隐性提交）。在我们建立了TRANSACTION_TAB表之后，我们就可以使用INSERT INTO SELECT语句向表中插入数据。

以下是当前HR数据库中会话的临时表和事务的临时表：

SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
LOCATIONS_INC
LOCATIONS_IOT
LOCATIONS_IOT_C
REGIONS
SESSION_TAB
 
TABLE_NAME
------------------------------
SYS_IOT_OVER_30463
TRANSACTION_TAB

7.2.5 其它表类型

这里有三种我们需要顺便提及的其它表类型，对它们进一步地讨论将会超出书本的范围。

可以将非常大的表分割成较小的片段（分区）建立分区表。对于应用，分区表实际上就像一个表，但是由于它们工作在独立的分区上，而不是整个表，所以可能需要辅助管理员。
簇表，或者通常也称为簇，是物理上存储在一起的2个或者多个表。因为这些表被认为总是会一起受到查询（使用SQL语句中的一些连接形式），所以会将表存储在相同的数据块中，而不是它们各自的数据块中。由于所有需要的行都存储在公共的数据块上，所以这可以帮助减少查询中所需的磁盘读取量。
散列簇（Hash clustered）表与在磁盘上将2个或者多个表实际存储在一起的簇表相似。两者之间的区别是Oracle用来存储和获取行的方法。在簇表中，会使用分离索引中存储的键值获取行，而在散列上，Oracle会使用散列函数来判断存储所要获取的行的数据块的位置。

7.3 表特性

当使用Oracle管理应用数据的时候，表的特性将会决定怎样建立表，怎样在磁盘上存储表，以及当表生成和可以使用之后，应用最终执行方式。在这一节中，我们将要讨论可以在CREATE TABLE和ALTER TABLE命令中使用，以规定应用中表行为的各种表属性。

7.3.1 TABLESPACE子句

在第5章中，我们学习到表空间是存储数据库对象的逻辑对象。当建立表的时候，必须将其放置在表空间中。这为存储表数据提供了一个“桶”，它可以通过在CREATE TABLE和ALTER TABLE命令中使用TABLESPACE子句来实现。然而要注意，TABLESPACE子句是可选的，没有明确规定TABLESPACE子句而建立的表会存放在建立表的用户账号的默认表空间中。我们要作为用户SCOTT连接数据库，并且使用USER_USERS视图判定默认表空间名称，来对其进行展示：

SQL> connect scott/tiger;
已连接。

SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
SYSTEM

用户可以看到SCOTT模式的DEFAULT_TABLESPACE是SYSTEM。如果我们现在建立FOO，而不规定TABLESPACE，那么我们就可以查询USER_TABLES视图来确定表的TABLESPACE_NAME，如下所示：

SQL> create table foo(
  2   a int
  3  )
  4  /
表已创建。

SQL> select table_name,tablespace_name
  2  from user_tables
  3  where table_name='FOO'
  4  /
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ---------------
FOO                            SYSTEM

这里的结果表明DEFAULT_TABLESPACE实际上是SYSTEM。为了规定表空间，我们需要删除表FOO，然后使用如下代码对其进行重新建立：

SQL> drop table foo;
表已丢弃。

SQL> create table foo(
  2   a int)
  3  tablespace users
  4  /
表已创建。

SQL> select table_name,tablespace_name
  2  from user_tables
  3  where table_name='FOO'
  4  /
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ---------------
FOO                            USERS

现在可以看到我们已经在USERS表空间中重新建立了表FOO。

要注意，如果没有USERS表空间，用户将会得到错误消息：

ORA-00959:tablespace ‘USERS’ does not exist.

用户可以使用如下ALTER USER语句对其进行修改（还要从管理账号中）：

alter user SCOTT quota unlimited on users;

注意：

可以注意到，在Oracle中建立用户的时候，可以规定用户的默认表空间。这个特殊的子句是可选的，如果忽略，默认值就是SYSTEM。

这个子句通常会被忽略，用户经常会在SYSTEM表空间中建立它们的所有对象。将用户的所有对象放入SYSTEM表空间，就像将用户的所有文件放入Windows上的C:\目录中或Unix计算机上的根目录（/）中一样。这将导致一系列的问题，包括：

数据库混乱。由于所有对象都在一个表空间中，所以数据库的管理性会变差。
由于必须从SYSTEM表空间的相同驱动器中读取应用的表数据，所以会导致性能问题（潜在的）。（表空间可以分布到多个磁盘/设备，但是这不是默认设置。）
空间争用。SYSTEM表空间是Oracle存储它自己操作实例所需数据的地方（例如数据词典）。在这种情况下对空间的争用将会破坏系统性能。

1 识别和消除SYSTEM入侵者

在为CREATE TABLE语句设置TABLESPACE子句之前，用户应该检查数据库中的用户账号，以确保它们没有将SYSTEM表空间设置为它们的默认表空间。在以下的查询中，我们将会寻找我们的数据库中所有违反了这个规则的模式。

SQL> connect scott/tiger;
已连接。

SQL> select username,default_tablespace,temporary_tablespace
  2  from dba_users
  3  where default_tablespace='SYSTEM' or temporary_tablespace='SYSTEM'
  4  /
USERNAME         DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ 
SYS                            SYSTEM                         TEMP
SYSTEM                         SYSTEM                         TEMP
DBSNMP                         SYSTEM                         TEMP
HR_AUDIT                       SYSTEM                         TEMP
ORACLE_ADMIN                   SYSTEM                         TEMP
SCOTT                          SYSTEM                         TEMP
OUTLN                          SYSTEM                         TEMP
WMSYS                          SYSTEM                         TEMP
ORDSYS                         SYSTEM                         TEMP
ORDPLUGINS                     SYSTEM                         TEMP
MDSYS                          SYSTEM                         TEMP
已选择11行。

就如用户所见，我们很多账号将SYSTEM作为默认表空间。由于这个例子中我们只关心用户SCOTT，所以将要修改SCOTT的默认表空间，以便可以将新表写到SYSTEM表空间以外的地方：

SQL> alter user scott default tablespace users
  2  /
用户已更改。

SQL> select default_tablespace,temporary_tablespace
  2  from dba_users
  3  where username='SCOTT'
  4  /
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ---------------------
USERS                          TEMP

如果用户SCOTT现在建立一个表，但是没有明确规定TABLESAPCE子句，默认情况下，就会将表数据存储在USERS表空间中：

SQL> connect scott/tiger;
已连接。

SQL> create table t(
  2   a int
  3  )
  4  /
表已创建。

SQL> select table_name,tablespace_name
  2  from user_tables
  3  where table_name='T'
  4  /
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ---------------
T                              USERS

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ -------------------------
BONUS                          SYSTEM
DEPT                           SYSTEM
EMP                            SYSTEM
FOO                            USERS
SALGRADE                       SYSTEM
T                              USERS
已选择6行。

注意：

不用为SYS或者SYSTEM模式修改默认表空间属性或者临时表空间属性。它们是数据库建立和管理的内部账号。

7.3.2 LOGGING和NOLOGGING

LOGGING和NOLOGGING是能够随CREATE TABLE语句使用的可选参数。术语日志记录（logging）是指Oracle重做日志，它记录了数据库中数据的所有改变。如果所发生的故障使数据不能从内存传递到数据库的管理文件，就可以从重做日志中获取这些改变。这可以防止数据丢失，提高可靠性。

当在CREATE TABLE语句中规定NOLOGGING的时候，就认为这个表是非日志记录表（nologging table）。在这个表上进行的操作可能会潜在导致数据库中更少的日志记录。

当建立非日志记录表的时候，重做日志生成会受到抑止，但是这只针对于特定操作。在这些操作期间，所改变的数据细节将不予考虑。然而，这并不是说不会建立重做日志。由于数据库的内部结构（特别是数据词典）正在改变，所以所有这种改变都会被记录。这会让那么认为使用NOLOGGING建立他们的表时，就不会有重做日志活动的管理员感到迷惑。除了以下所列的活动外，表中所发生改变的重做日志活动如常。能够利用NOLOGGING子句的活动如下所示：

CREATE TABLE AS SELECT
SQL*LOADER直接路径载入
直接路径插入（通过/*+APPEND */提示）

不要将LOGGING子句与NOLOGGING子句的目的混淆。常规的表操作都会记录在重做日志中，以便在实例故障的时候进行恢复。就如我们讨论的，唯一避免重做日志的方式就是使用全局临时表。

7.3.3 STORAGE子句

在数据库中有效管理空间消耗将会直接影响数据库增长和存储数据的能力。当用户在Oracle中建立对象的时候（例如表和索引），用户就可以规定对象怎样消耗磁盘上的空间。

当建立消耗存储的对象时，它就会存储在我们在第5章中介绍的称为盘区的逻辑对象中，随着对象的增长，它们会消耗起来越多的盘区。

在Oracle 8i以前，盘区和空间分配通常都要在数据词典中管理。当Oracle要建立新盘区的时候，就要对数据词典表进行低层查询，寻找目录表空间中的下一个可用盘区。当分配了盘区之后，还必须要使用新的盘区信息对数据词典进行更新。目标表空间称为词典管理（dictionary-managed）表空间。如果同时要分配大量盘区，而Oracle一次只能够在数据词典中分配一个盘区，这种方式就会导致性能问题。

当规定CREATE TABLE命令的存储子句的时候，用户可以使用如下参数：

INITIAL——这是所建立的第一个盘区的大小。
NEXT——在表的第一个盘区堆满之后，NEXT参数就会告诉Oracle为随后的盘区分配的空间大小。
PCTINCREASE——对于不能够确定对象增长需求数据的管理员，PCTINCREASE参数可以提供“不断增长”的下一个盘区的容量。每次分配盘区的时候，NEXT大小都要根据PCTINCREASE比例增长。然而，这意味着每次分配另一个盘区的时候，用户的盘区容量都会增长，通常建议将PCTINCREASE设置为0.
MINEXTENTS——当词典管理空间中建立表的时候，管理员可以告诉Oracle在建立表的时候分配多个盘区。对于INITIAL和NEXT大小设置为1MB，PCTINCREASE设置为0，MINEXTENTS参数为5的表，意味着建立的时候分配5个1MB的盘区（5MB空间）。
MAXEXTENTS——这个参数规定了可以为表分配的盘区数量的上限。对于INITIAL和NEXT大小设置为1MB，PCTINCREASE设置为0，MAXEXTENTS参数为10的表，意味着表不能够超过10MB的大小（除非管理员改变表的MAXEXTENTS属性）。

在Oracle 8i中，Oracle引入了局域管理（locally managed）表空间，使得INITIAL、NEXT、PCTINCREASE、MINEXTENTS和MAXEXTENTS存储属性都有些过时。这些类型的表空间为管理员提供了选择，可以让Oracle管理盘区。而不是手工配置和管理他们的表存储。

1 存储概要

局域管理表空间免除了数据库管理员进行盘区分配和规模维护的负担。在Oracle 9i中，用户也有一些需要使用词典管理表空间的理由。通常，使用统一盘区容易要比自动分配盘区更好一些，因为使用统一盘区容易可以让数据库中的自由盘区能够为表空间中的其它段使用。

如果用户需要利用用户表空间的存储属性，就应该对其进行规定，而不是使用默认值。这样就可以确保用户能够控制在表空间中存储的对象类型，以及它们将要消耗的空间。不要在对象层次（表、索引等等）规定存储属性，而是要让它们从表空间继承它们的存储子句。

7.3.4 CACHE和NOCACHE

当在Oracle中执行全表搜索的时候，读入缓存的数据块将会存储在最近最少使用列表（LRU）的最近最少使用的一端。这意味着只要执行“常规”查询，必须向缓存中读取数据的时候，就会将这些数据块换出缓存。

当建立表的时候，CACHE子句可以忽视这种行为。当使用CACHE子句建立的表上执行全表搜索的时候，会将数据块读入缓存，并且放置到LUR的最近最常使用的一段。

应该为小搜索表，以及因为这样或者那样的原因没有使用索引的表规定CACHE。没有索引的读取经常会导致全表搜索，由全表搜索读取的数据块会很快被换出SGA。如果要相当频繁地访问用户表，那么CACHE子句就可以帮助最小化数据块的物理读取。

NOCACHE是建立表的时候的默认值。没有明确规定CACHE子句时所建立的表就是NOCAHE表。从这样的表中读取的数据块通常会被交换出SGA。

7.4 修改表

在Oracle数据库中使用表并不困难，但是通过一次尝试就建立一个完美的表几乎不可能的。在建立了表，并且开始使用之后，很有可能会遇到没有预计的需求或者问题。所以，Oracle拥有了非常详细的ALTER TABLE语句来改变所有方面的表属性。

表可以进行许多类型的修改，详细地讨论这些问题已经超出了本章的范围。与此相反，这部分将会涵盖一些最常使用的ALTER TABLE命令，以及怎样有效地使用它们。它们是：

在表中增加、修改或者删除列
重命名表
将表移动到新的表空间
改变表的存储属性
改变表的特性，例如LOGGING和NOLOGGING或者CACHE和NOCACHE

7.4.1 改变表中的列

在项目的开发和维护阶段，需要对表进行修改，以适应新的数据需求和各种功能改变。从性能上讲，删除表，并且对其进行重新建立是不可行的，所以Oracle提供了增加新列、修改已有列，以及从已有表中删除列而不影响表中其它数据的能力。

在以下示例中，我们将要建立一个表，并且使用ALTER TABLE命令去修改表列。我们首先会建立表PEOPLE，并且插入一些值：

SQL> connect hr/hr
已连接。

SQL> create table people(
  2  employee_id number(9),
  3  first_name varchar2(15),
  4  last_name varchar2(20),
  5  email varchar2(25),
  6  constraint pk_people primary key(employee_id)
  7  )
  8  /
表已创建。

SQL> insert into people
  2  values(1,'Tom','Kyte','tkyte@us.oracle.com');
已创建 1 行。

SQL> insert into people
  2  values(2,'Sean','Dillon','sdillon@us.oracle.com');
已创建 1 行。

SQL> insert into people
  2  values(3,'Christopher','Beck','clbeck@us.oracle.com');
已创建 1 行。

SQL> commit;
提交完成。

为了验证记录插入成功，我们要查询PEOPLE表，如下所示：

SQL> select * from people;
EMPLOYEE_ID FIRST_NAME      LAST_NAME            EMAIL
----------- --------------- -------------------- ---------------------
          1 Tom             Kyte                 tkyte@us.oracle.com
          2 Sean            Dillon               sdillon@us.oracle.com
          3 Christopher     Beck                 clbeck@us.oracle.com

就如用户所见，我们的表现在已经填充了数据。我们要做的第一件工作就是使用如下语句增加一个PHONE_NUMBER列：

SQL> alter table people
  2  add(
  3  phone_number varchar2(10)
  4  )
  5  /
表已更改。

SQL> select * from people;
EMPLOYEE_ID    FIRST_NAME      LAST_NAME    EMAIL   PHONE_NUMB
----------- --------------- -------------------- ------------------------- ----
          1 Tom             Kyte                 tkyte@us.oracle.com
          2 Sean            Dillon               sdillon@us.oracle.com
          3 Christopher     Beck                 clbeck@us.oracle.com

就如用户所见，由于我们已经向表中增加了新的列，但是没有修改已有的行，所以，数据库中没有行会包含新列数据。

7.4.2 NOT NULL列约束

只有当表中没有记录的时候，用户才能够规定说明NOT NULL作为约束的列。这是因为当在列上应用NOT NULL约束的时候，Oracle就会尝试验证表中的所有行。如果已经存在记录，那么这些记录就不能够通过这些验证，因此也就不能够增加约束，进而也不能够增加列。然而，有一个解决这个问题的变通方式。我们可以首先向表中增加所需的列（在这个例子中是SSN）。

SQL> alter table people
  2  add(
  3  ssn number(9)
  4  )
  5  /
表已更改。

SQL> update people
  2   set ssn=234567890
  3  where employee_id=1;
已更新 1 行。

SQL> update people
  2   set ssn=345678901
  3  where employee_id=2;
已更新 1 行。

SQL> update people
  2  set ssn=456789012
  3  where employee_id=3;
已更新 1 行。

SQL> alter table people
  2  modify(
  3  ssn number(9) not null
  4  )
  5  /
表已更改。

SQL> desc people;
名称                     是否为空? 类型
----------------------------------------------------------------------------
 EMPLOYEE_ID  NOT NULL    NUMBER(9)
 FIRST_NAME                          VARCHAR2(15)
 LAST_NAME                           VARCHAR2(20)
 EMAIL                                    VARCHAR2(25)
 PHONE_NUMBER                   VARCHAR2(10)
 SSN  NOT NULL                    NUMBER(9)

就如用户所见，NOT NULL约束现在已经成功应用于SSN列。

这里要简单提及的是，如果用户要通过约束数据类型来修改用户表列例如纷乱数据类型的长度，那么如果列中已有的数据要违反遵循改变的列数据类型时，Oracle就会拒绝改变。

7.4.3 删除列以及标注不用列

用户不仅可以修改已经存在于表中的列，而且还可以完全删除它们。在Oracle 8i以前，表中所有没有使用的列都作为附加负荷承担，或者使用CREATE TABLE AS SELECT语句重新建立没有不想要的列的新表。然而，在Oracle 8i及其以上版本中，就可以简单使用如下语句对列进行删除：

ALTER TABLE <table name> DROP COLUMN

这个操作会将表重新写入到磁盘，并且移走旧有的列数据，这是一种“回收”曾经由不想再使用的列所使用的空间的方式。

ALTER TABLE <table name> SET UNUSED COLUMN <column name>

这个命令与ALTER TABLE <table name> DROP COLUMN命令有所区别，它不会对表进行重写，也不会收回空间。在这个语句执行之后，列只会被简单忽略。因为在列被完全删除之前，不能够对数据进行覆写，所以这会导致区域的数据存储的丢失。如果必须要回收这些丢失的存储空间，就要对表进行重新组织（列会被删除）。

在ALTER TABLE的DROP COLUMN和SET UNUSED COLUMN变种中，用户可以一次删除或者标注多个列。为了展示这些内容，我们要作为用户SCOTT注册，并且使用HR模式的表，我们首先要为SCOTT赋予HR所拥有的表上的必要特权：

SQL> connect hr/hr
已连接。

SQL> grant select on departments to scott;
授权成功。

SQL> grant select on locations to scott;
授权成功。

SQL> grant select on countries to scott;
授权成功。

SQL> grant select on regions to scott;
授权成功。

现在，我们可以建立DEPARTMENTS表，然后执行SELECT语句，验证在我们的表中已经包含了所需要的信息：

SQL> connect scott/tiger
已连接。

SQL> create table departments as
  2  select d.department_id,d.department_name,d.manager_id,d.location_id,c.country_name,r.region_name
  3  from hr.departments d,hr.locations l,hr.countries c,hr.regions r
  4  where d.location_id=l.location_id
  5  and l.country_id=c.country_id
  6  and c.region_id=r.region_id
  7  /
表已创建。

SQL> select department_name,country_name,region_name
  2  from departments
  3  order by 3,2,1
  4  /
DEPARTMENT_NAME                COUNTRY_NAME                             REGION_NAME
------------------------------ ---------------------------------------- --
Marketing                      Canada                                   Americas
Accounting                     United States of America                 Americas
Administration                 United States of America                 Americas
.
.
.
Public Relations               Germany                                  Europe
Human Resources                United Kingdom                           Europe
Sales                          United Kingdom                           Europe
已选择27行。

然而，在建立了表之后，我们现在意识到不需要在DEPARTMENTS表中维护COUNTRY_NAME或REGION_NAME列，这些值可以在其它表中得到。出现这种考虑，我们需要删除这些列。我们可以一次删除一列：

SQL> alter table departments
  2  drop column country_name
  3  /
表已更改。

SQL> alter table departments
  2  drop column region_name
  3  /
表已更改。

或者实际上，我们也能够在同一个语句中同时删除2列：

SQL> --我们可以增加回旧字段!
SQL> alter table departments
  2  add(
  3  country_name varchar2(40),
  4  region_name varchar2(15)
  5  )
  6  /
表已更改。

SQL> alter table departments
  2  drop(
  3  country_name,region_name)
  4  /
表已更改。

在使用的情况下，用户可能无法重写它们（特别它们一直在使用的时候）。用户可以不删除它们，而是将它们设置为UNUSED，随后，用户可以将它们一起删除。在DEPARTMENTS的例子中，我们可以使用如下方式对其进行实现：

SQL> alter table departments
  2  add(
  3  country_name varchar2(40),
  4  region_name varchar2(15)
  5  )
  6  /
表已更改。

SQL> alter table departments
  2  set unused(
  3  country_name,region_name)
  4  /
表已更改。

为了确定在我们的表中有多少没有使用的列，我们可以USER_UNUSED_COL_TABS数据词典视图，如下所示：

SQL> select * from user_unused_col_tabs;
TABLE_NAME                          COUNT
------------------------------ ----------
DEPARTMENTS                             2

这个表不需要全天在线，或者当用户计划维护的时候，用户就可以使用我们在前面看到的命令，从数据库中将这些列删除，回收存储空间：

ALTER TABLE departments DROP UNUSED COLUMNS

要注意，如果用户使用了编码向DEPARTMENTS表中插入记录的时候：

insert into departments(..) values(..)

如果COUNTRY_NAME列或者REGION_NAME列已经删除（或者标记为不可用）的时候，这些代码就会中断。

7.4.4 重命名表

改变表的名称是一个相对容易执行的任务。ALTER TABLE命令如下所示：

SQL> connect hr/hr
已连接。

SQL> alter table people
  2  rename to employees
  3  /

SQL> alter table people
  2  rename to people01
  3  /
表已更改。

注意：

对表进行重命名非常容易，但是影响却非常大。在需要对表的名称进行修改的时候，要格外小心。尽管Oracle可以自动更新数据词典中的外键、约束定义以及表关系，但是它还不能够更新数据库中的存储代码模块、存储报告或者查询，或者客户应用。对于后来这些使用表的对象（例如客户应用），当表重命名之后，就会失败。

7.4.5 将表移动到新表空间或者存储

存储表的方式以及读取表的磁盘都会影响数据库的整体性能。通常，整个数据库都要使用预先设计的附属表存储体系结构构建。可以根据数据库访问表的方式、其它数据库的对象存放地点以及存储介质的物理结构来确定表的所属空间。表的存储属性要从所建立表的类型，以及怎样在应用中使用它的角度进行指定。

这里是管理员希望改变表的存储属性的一些原因，包括：

表处于不适合的表空间中
应用改变使用表的方式
存储在表中的数据发生改变（换句话说，加入了新列或者改变了已有的列）

ALTER TABLE <table name> MOVE

语句可以实现这个目的。为了展示这一点，我们来浏览我们的数据库，查看是否有表在SYSTEM表空间中：

SQL> connect scott/tiger;
已连接。

SQL> select tablespace_name,table_name
  2  from user_tables
  3  where table_name in ('EMP','DEPT','BONUS','SALGRADE')
  4  order by 1,2
  5  /
TABLESPACE_NAME                TABLE_NAME
------------------------------ ---------------------------
SYSTEM                         BONUS
SYSTEM                         DEPT
SYSTEM                         EMP
SYSTEM                         SALGRADE

就如我们早先解释的，将表存储在这个表空间中通常是不好的习惯，我们想要将这些表移动到另一个表空间中。为了看到数据实际从一个段移动到另一个段，我们将会在我们执行ALTER TABLE语句的前后，查看数据词典视图USER_SEGMENTS：

SQL> select segment_name,tablespace_name
  2  from user_segments
  3  where segment_name='EMP'
  4  /

SEGMENT_NAME       TABLESPACE_NAME
----------------------------------------------------------------------------
EMP                            SYSTEM

SQL> alter table emp move
  2  tablespace users
  3  /
表已更改。

SQL>  select segment_name,tablespace_name
  2   from user_segments
  3   where segment_name='EMP'
  4  /
SEGMENT_NAME       TABLESPACE_NAME
----------------------------------------------------------------------------
EMP                            USERS

当我们将表从SYSTEM表空间移动到USERS表空间的时候，也会将段从一个表空间移植到另一个表空间。由于段实际上是在数据文件中存储的数据块，而USERS表空间与SYSTEM表空间具有不同的数据文件，所以也会将数据在物理上移动到另一个数据文件。

7.4.6 改变不同的表特性

有的时候，当建立表的时候，不可能知道在它们所支持的应用的生命周期期间，施加给这些表的所有要求。我们试图尽可能具有前瞻性地构建表，但是我们会不时地意识到通过改变的属性可以获得更好的性能，或者消耗更少的资源。

例如，如果应用拥有会频繁进行完全搜索的表，而且这是必须的行为，那么将它的数据志缓存在缓存内存区域中就会获益。如果由于执行其它查询，要将数据块交换出内存，那么这个表的数据就要被读入内存、交换出内存、再次读入内存。与此相反，我们在这个表上设置CACHE属性会通知Oracle将数据块放到“最近最少使用”列表的“最近最常使用”一端，进而强制Oracle将数据块保留在内存中（至少可以保留更长一段时间）。这可以使用如下ALTER TABLE命令实现：

alter table <table name> [cache|nocache]；

相同的方法也可以用于表的LOGGING和NOLOGGING特性：

alter table <table name> [logging|nologging]；

7.4.7 ALTER TABLE总结

本章已经涵盖了一些用户最常对表进行的改变类型。它确实没有包含Oracle可以让它的用户进行改变的所有。表的大多数属性都可以进行修改。而且，对于我们在本章前面讨论的不同类型的表，还可以改变那些属于这些表类型的属性。ALTER TABLE命令的完整文档可以在Oracle SQL Reference中找到。

7.5 删除表

DROP TABLE命令的语法如下所示：

DROP TABLE <TABLE_NAME>[ CASCADE CONSTRAINTS ];

在以下救命中，我们要建立和生成表DROP_ME，然后删除它：

SQL> create table drop_me(
  2  a int,
  3  b int
  4  )
  5  /
表已创建。

SQL> insert into drop_me values(1,1);
已创建 1 行。

SQL> insert into drop_me values(1,2);
已创建 1 行。

SQL> insert into drop_me values(2,1);
已创建 1 行。

SQL> drop table drop_me;
表已丢弃。

删除表中的所有数据，和从数据库中删除表具有根本的不同。即使用户使用DELETE命令从表中删除了所有记录，表仍然会存在，在所有记录被删除之后还可以使用。然后，当用户删除表的时候，表就不复存在，向表中插入记录的唯一方法就是使用相同的表特性建立一个新表。

CASCADE CONSTRAINTS

DROP TABLE 命令有一个唯一的可选参数，称为CASCADE CONSTRAINTS。这个参数可以用于那些所拥有的外键引用所删除表的表。如果没有规定CASCADE CONSTRAINTS，那么当管理员试图删除在子表中具有记录的表时，操作就会失败，并且会向用户发出一个错误。通过规定CASCADE CONSTRAINTS，将会删除所有子表外键。

7.6 TRUNCATE TABLE

TRUNCATE TABLE是用来删除所有数据，但是不删除表本身的DDL语句。为这个表提供的索引也可以被截去。TRUNCATE TABLE能够用于堆组织表，索引组织表，以及临时表。使用如下语法就可以执行TRUNCATE TABLE命令：

TRUNCATE TABLE [ SCHEMA.] <table name> [ DROP STORAGE |  REUSE STORAGE]

TRUNCATE TABLE是从表中删除所有记录的快速方法，而且因为它不生成回滚数据，所以它也比使用DELETE命令更有效。

当使用TRUNCATE TABLE的时候，有一些需要注意的要点：

为了执行TRUNCATE TABLE命令，用户必须具有DROP TABLE特权。
在使用TRUNCATE TABLE之前，必须禁用所有子表外键。如果有任何表引用了正在被删除的表，那么语句就会失败。（子引用外键不必禁用）
在TRUNCATE TABLE操作期间，不会激活ON DELETE触发器。
由于TRUNCATE TABLE是一个DDL语句，所以在它执行前后会进行提交，不能够被回滚。

7.6.1 DROP STORAGE或者REUSE STORAGE

在表的存续期间，会分配盘区来存储生成表的数据行。表可能只分配了由表的MINEXTENTS存储属性所指定的盘区数量，或者它也可以根据插入表中的行的数量、各行的大小以及盘区的容量，分配了成百上千的盘区。当使用TRUNCATE TABLE语句的时候，管理员必须要决定是要将这些盘区返还给表空间，以便由其它的对象所使用，还是保留这些盘区让这个表来使用。

1 DROP STORAGE

DROP STORAGE是TRUNCATE TABLE的默认行为。当使用DROP STORAGE的时候，就会恢复表的最初存储特性。这意味着只有最初为表分配的盘区（MINEXTENTS）会被保留以用于表中的新行，所有附加的盘区都会被释放，以便表空间中的其它对象使用。如果表不会返回到它的最初大小，或者要花很长的时候才会增长回它最初的大小，就应该使用DROP STORAGE。这可以确保在表空间中长时间不会有大块的存储空间，或者在更糟的情况下，永远不能够被其它对象所使用。

REUSE STORAGE

如果使用了REUSE STORAGE子句，那么所有为表存储分配的盘区都会保留，表将会使用它们向表中插入新行。对于快速增长的表，为了要为插入的新行清空表，就可以使用TRUNCATE语句快速删除行，并且应该使用REUSE STORAGE。这可以让Oracle不必为这个表不断分配新的盘区。

7.6.2 截取临时表

当截取临时表的时候，只会删除用户在会话期间插入表的行。当进程需要在会话结束之前，清空并且重新生成表的时候，就可以使用这种非常好的方式来重置特定于会话的临时表。在专用于事务处理的临时表上使用TRUNCATE TABLE不甚明智，因为COMMIT将会更有效率在特定于事务处理的临时表中，所以COMMIT命令将会删除表中的行，但是空间不能够重用。

7.7 小结

在讨论索引组织表、外部表、以及临时表类型的时候，我们已经展示了怎样根据用户的特定需求建立表特性，进而辅助提高性能。使用这些表类型时所获得的经验将会极大地加强用户数据库的效率。

文章根据自己理解浓缩，仅供参考。

摘自：《Oracle编程入门经典》清华大学出版社 http://www.tup.com.cn

会员力量，点亮园子希望

刷新页面返回顶部

夜雨瞳 <strong>人致力于一个目标，一种观念......是人在生活过程中追求完整之需要的一种表现 --- 罗曼.罗兰</strong>

Oracle编程入门经典 第7章 表

7.1 介绍Oracle中的表

7.2 表类型

7.2.1 堆表

试验：建立堆组织的表

工作原理

7.2.2 外部表

试验：建立和使用外部表

工作原理

1 Type（或者访问驱动器类型）

2 DEFAULT DIRECTORY

3 BADFILE和NOBADFILE

4 LOGFILE和NOLOGFILE

7.2.3 索引组织表

1 COMPRESS和NOCOMPRESS

2 OVERFLOW

3 INCLUDING

4 PCTTHRESHOLD

7.2.4 临时表

试验：建立临时表

工作原理

7.2.5 其它表类型

7.3 表特性

7.3.1 TABLESPACE子句

1 识别和消除SYSTEM入侵者

7.3.2 LOGGING和NOLOGGING

7.3.3 STORAGE子句

1 存储概要

7.3.4 CACHE和NOCACHE

7.4 修改表

7.4.1 改变表中的列

7.4.2 NOT NULL列约束

7.4.3 删除列以及标注不用列

7.4.4 重命名表

7.4.5 将表移动到新表空间或者存储

7.4.6 改变不同的表特性

7.4.7 ALTER TABLE总结

7.5 删除表

CASCADE CONSTRAINTS

7.6 TRUNCATE TABLE

7.6.1 DROP STORAGE或者REUSE STORAGE

1 DROP STORAGE

7.6.2 截取临时表

7.7 小结

About

Oracle编程入门经典第7章表