oracle 11g 的表

1. 高水位线

高水位线（high-water mark，HWM）是一个很有趣的概念，但是也是一个非常重要的概念。顾名思义，高水位线有点类型于水文监测站里测水深度的标杆一样，当水涨的时候，水位线随之上升，并在标杆留下一个水印痕，这个水印痕就是高水位线。

在数据库中，上述比喻很恰当。如果把表想象成一个平面结构，或者想象成从左到右依次排开的一系列块，高水位线就是包含了数据的最右边的块。如下图所示：

在这里插入图片描述

当表刚创建时，HWM 位于表的第一个块中。过一段时间后，随着在这个表中放入数据，而且使用了越来越多的块，HWM会升高。但当我们删除了表中的一些（甚至全部）行，可能就会出现许多块不再包含数据，但仍然处于 HWM 之下，而且会一直保持在 HWM 之下。记住：HWM 永远不会下降，除非使用 rebuild、truncated 或 shrunk 这个对象（shrinking是 10g 的一个新特性，仅 ASSM即自动段存储管理支持）。

HWM很重要，因为Oracle在全表扫描时会扫描HWM之下的所有块，即使其中不包括任何数据。这会影响full scan的性能，特别是当HWM之下的绝大多数块都为空时。

2. PCTFREE

为一个块保留的空间百分比，表示数据块在什么情况下可以被insert，默认是10，表示当数据块的可用空间低于10%后，就不可以被insert了，只能被用于update；即：

当使用一个block时，在达到pctfree之前，该block是一直可以被插入的，这个时候处在上升期。

预留的空间主要是为了用户进行 update 操作而导致占存空间变大时，可以方便地使用这部分预留空间进行存储数据。

3. PCTUSED

PCTUSED 是指当块里的数据低于多少百分比时，又可以重新被 insert，一般默认是40，即40%，即：当数据低于 40% 时，又可以写入新的数据，这个时候处在下降期。

假设你一个块可以存放100个数据，而且PCTFREE 是10， PCTUSED是40，则：不断的向块中插入数据，如果当存放到90个时，就不能存放新的数据，这是受pctfree来控制，预留的空间是给UPDATE用的。

当你删除一个数据后，再想插入个新数据行不行？不行，必须是删除41个，即低于40个以后才能插入新的数据的，这是受 pctused来控制的。

注意：如果表空间上启用了ASSM，在建立表的时候，只能指定PCTFREE，否则可用指定PCTFREE和PCTUSED。

4. 普通表

4.1 移动表

移动表move，从一个表空间移动到另一个表空间，可以清除表里的碎片。优点是可以清除数据块中的碎片，降低HWM；缺点则是在 move 过程中，表上不能有应用。move之后，表上的索引需要重建。语法如下：

1	alter table 表名 move [tablespace 表空间名字];

4.2 收缩表

收缩表shrink，将数据行从一个数据块移动到另一个数据块，分为2个阶段：收缩、降低HWM；在收缩阶段，可以对表进行DML操作，在降低HWM阶段，不能对表进行DML操

作。

前提：1. 表所在的表空间使用了ASSM（默认启用）；表上启用了 row movement。

语法如下所示：

-- 启用 row movement
alter table 表名 enable row movement
-- 收缩表
alter table 表名 shrink space [cascade];

4.3 截断表

截断表truncate，将表中的记录全部删除，保留表的结构。释放表所占用的全部数据块，并把HWM调整到最低，而且不能回滚。

4.4 删除表

1 2	-- purge 表示不存储到回收站 drop table 表名 [cascade constraints] [purge];

4.5 删除列

当数据量比较大的时候，使用 drop columns 语句来删除列速度会比较慢。这个时候推荐使用如下语句来删除指定列：

-- 先失效掉指定列
alter table 表名 set unused column 列名;
-- 删除无效的列
alter table 表名 drop unused columns;

5. 索引组织表 IOT

区别于普通表的无序组织方式，IOT(Index Organized Table)表必须有主键，是有序的表，其中的数据按照主键进行存储和排序。使用堆组织表（普通表）时，我们必须为表和表主键上的索引分别留出空间。而IOT不存在主键的空间开销，因为IOT的数据存储在与其关联的索引中，索引就是数据，数据就是索引，二者已经合二为一。

IOT表中，表的数据存放在索引块中，所以如果通过主键索引访问表时，只需要读取一个块即可。而如果通过主键索引访问普通表，至少需要读取两个块，一个是索引块、一个是数据块。对于经常通过主键访问数据的表来说，适合使用IOT表。

如下是索引表的创建示例：

-- 创建表是使用 organization index 指定是 IOT 表
create table iot_student(     
    sno int, 
    sname varchar2(100),
    sage int,     constraint pk_student primary key(sno)
) organization index 
[ pctthreshold  30 overflow tablespace users ];

因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。

说明： pctthreshold制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段，即overflow指定存储空间中去，所以pctthreshold是保留在索引块里的数据量占整个索引块的大小百分比，从0到50%。默认的 pctthreshold的值是50，即50%。

6. 簇表

两个相互关联的表的数据，同时放到一个簇数据块中，当以后进行关联读取时，只需要扫描一个数据块就可以了，极大的提高了效率。簇表分为索引簇表和哈希簇表两类。

索引簇表的创建步骤：

建立簇段 cluster segment；
基于簇，创建两个相关表，每个表都关联到cluster segment上；
为簇创建索引。

簇表创建示例如下所示：

-- 创建簇
create cluster scott.cluster1(code_key number);
-- sno1 的类型与 cluster 簇的 code_key 对应
create table scott.student (sno1 number, sname  varchar2(10)) cluster scott.cluster1(sno1);
-- sno2 的类型与 cluster 簇的 code_key 对应
create table scott.address (sno2 number, zz varchar2(10)) cluster scott.cluster1(sno2);
-- 为簇创建索引
create index index1 on cluster scott.cluster1;

7. 临时表

存放临时数据，可以使用临时表；临时表被每个session 单独使用，即：不同session看到的临时表中的数据可能不一样。如果在退出session时删除临时表中的数据，可以在创建临时表的时候使用on commit preserve rows；如果在用户commit或rollback时删除临时表中的数据，则可以使用on commit delete rows；

如下是创建临时表的示例语句：

1	create global temporary table tmp_student(sno int, sname varchar2(10)) on commit preserve rows;

如何理解临时表被每个 session 单独使用呢？假设现在使用开启 cmd 窗口登录 scott 账号创建一个临时表 tmp_student，然后再另起一个 cmd 窗口登录 scott 账号。显然这属于两个 session 会话。

虽然两个会话都可以访问到临时表 tmp_student，但是会话1向临时表插入的数据其他会话是无法访问到的。这就是临时表被每个 session 会话单独使用的意思。

从v$sort_usage中查看正在使用临时表空间的session信息和SQL语句的ID号，从v$sort_segment中查看临时表空间中的段的使用情况。临时表在临时表空间中保存。

1
2
3

-- 查看临时表信息
-- 表名，是否是临时表，on commit preserve rows 或 on commit delete rows 模式标识
select u.table_name, u.temporary, u.duration from user_tables u where u.table_name = 'TMP_STUDENT';

其中使用 on commit preserve rows 创建的临时表，duration 字段的值为 SYS$SESSION，否则是 SYS$TRANSACTION。