Oracle学习1205表

一、理解11g中，与表相关的几个概念：高水位线、PCTFREE、PCTUSED

1，高水位线（high-water mark,HWM)，非常重要，有点类型于水文监测站里测水深度的标杆一样，当水涨的时候，水位线随之上升，并在标杆留下一个水印痕，这个水印痕就是高水位线。

1-1，在数据库中，如果把表想象成一个平面结构，或者想象成从左到右依次排开的一系列块，高水位线就是包含了数据的最右边的块。

1-2，当表则创建时，HWM位于表的第一个块中。过一段时间后，随着在这个表中放入数据，而且使用了越来越多的块，HWM会升高。但当我们删除了表中的一些（甚至全部）行，可以就会出现许多块不再包含

数据，但仍然处于HWM之下，而且会一直保持在HWM之下。记住：HWM永远不会下降，除非使用rebuild(重建表）、truncated(删除表中的所有内容) 或shrunk（收缩）这个对象（shrinking是10g的一个新特性

，仅ASSM支持）。

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

2，PCTFREE 表示剩余空间，当用户执行insert操作时，数据块应该保留多少的 free 空间；如果 pctfree = 20，表示一个数据块要保留 20% 的空间是空的状态，作用就是防止用户做 update 时会

因为对数据的修改增大时，数据块装不下时，执行起来会变的异常困难； show parameter db_block_size 可以得到数据块的大小

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

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

3，PCTUSED 表示容许数据块有多少空闲的空间，如果设置 pctused = 40 ，表示在用户进行delete操作时，如果数据块的使用率低于 40% 的话，那么，就可以让此数据块在原来已经满了的情况

下又可以进行 insert操作，当然增加的数据也不能占用 pctfree 的空间

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

注意：如果表空间上启用了ASSM（自动段存储管理，简称自动段管理），在建立表的时候，只能指定 PCTFREE，否则可用指定 PCTFREE 和 PCTUSED。

4，可以用WEB界面的服务器/表空间中的段管理一列，如果是AUTO，表示自动，如果是MANUAL，表示手动

二、理解11g中，表的几种类型（普通表，分区表，索引组织表IOT，簇表，临时表，嵌套表、对象表等）

1，普通表

create tablespace testtbs datafile 'D:\app\Administrator\oradata\orcl\testtbs01.dbf' size 20m autoextend on;

表示创建一个表空间 testtbs，文件放在D盘下的那个地址，大小为 20m,可以自动增长

1-1，create table t1(id int,name varchar2(20)) tablespace testtbs;表示创建名为 t1，表空间为 testtbs

1-2，从PL/SQL的命令窗口 select * from dba_extents d where d.owner='scott' and d.segment_name='t1';

表示查询 scott 用户下的 t1 表的表空间被分配了多大内存

1-3，扩展一个表所占用的空间 alter table scott.t1 allocate extent (datafile 'D:\app\Administrator\oradata\orcl\TESTTBS01.DBF' size 1m);

表示将 scott 用户下的 t1 表的表空间扩大为 1m

1-4，移动表 move ，从一个表空间移动到另一个表空间，也可以在同空间使用，可以清除表里的碎片：alter table t1 move [tablespace users];

1-4-1，优点：清除数据块中的碎片，可以降低HWM(高水位线）

1-4-2，缺点：move过程中，表上不能有应用。move 之后，表上的索引需要重建

注意：如果要查看表的数据块信息，必须先对表进行分析：analyze table t2 compute statistics for table;(可以在sqlplus中执行）

然后可以执行 select u.table_name,u.blocks,u.num_rows from user_tables u where u.table_name='t2'（可以在PL.SQL的命令窗口执行）;表示查询表的已被占用的数据块数量及行记录数

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

alter table t2 shrink space [cascade];表示对t2表进行收缩的操作

注意：前提：表所在的表空间使用了ASSM。表上启用了 row movement : alter table t2 enable row movement;

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

1-7，删除表 drop; drop table t2 [cascade constraints(表示所有约束)] [purge(表示直接删除，不进入回收站）]

1-8，删除列，一般在数据库处在频繁使用的情况下，数据量很大时，可以使用如下操作；

1-8-1，alter table t2 set unused column 列名；表示将这一列不再使用，效率是非常高的

1-8-2，然后再使用 alter table t2 drop unused columns；表示删除不再使用的列及其内容

2，索引组织表IOT

2-1，区别于普通表的无序组织方式，IOT(Index Organized Table)表必须有主键，是有序的表，其中的数据按照主键进行存储和排序。

2-2，使用堆组织表时，我们必须为表和表主键上的索引分别留出空间。而 IOT 不存在主键的空间开销，因为 IOT 的数据存储在与其关联的索引中，索引就是数据，数据就是索引，二者已经合二为一。

2-3，IOT 表中，表的数据存放在索引块中，所以如果通过主键索引访问表时，只需要要读取一个块即可。而如果通过主键索引访问普通表，至少需要读取两个块，一个是索引块，一个是数据块。

2-4，对于经常通过主键访问数据的表来说，适合使用 IOT 表。

如何创建 IOT 表，例

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

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

从 0 到 50% 。默认的 pctthreshold 的值是 50 ，即 50%；

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

3-1，簇表分为索引簇表和哈希簇表两类。

3-2，索引簇表的创建步骤：

　　A，建立簇段

　　B，基于簇，创建两个相关表，每个表都关联到 cluster segment 上

　　C，为簇创建索引

说明：在创建之后，针对查询时，如 where 之后的条件为 sno1=sno2 时，速度会很快*

3-3，select * from user_clusters；可以查询已创建了的簇表

3-4，select * from user_clu_columns；可以查询已经创建了的簇表的字段是关联在哪些普通表的字段上的

注意：删除簇 drop cluster 簇名；删除之前，必然要删除与其关联的表

小常识：清空回收站 purge recyclebin;

4，临时表：存放临时数据，可以使用临时表；临时表被每个 session 单独使用，即：不同 session 看到的临时表中的数据可能不一样。

4-1，如果在退出 session 时删除临时表中的数据，可以使用 on commit preserve rows；如果用户 commit 或 rollback 时删除临时表中的数据，可以使用 on commit delete rows;

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

4-3，临时表在临时表空间中保存。

4-4，创建临时表，create global temporary table tmp_student(sno number, sname varchar2(20), sage number) on commit preserve rows;

表示创建一个名为 tmp_student 的临时表，临时表可以被多个 session 使用，每个 session 只能看到由当前会话所插入的数据，当一个 session 断开连接时（disconnect)，由当前 session 所插入的数据将会

自动清除，另外，只有当所有的 session 都断开连接时，临时表才可以被删除

4-5，创建临时表，create global temporary table tmp_student(sno number, sname varchar2(20), sage number) on commit delete rows;

表示创建一个名为 tmp_student 的临时表，临时表可以被多个 session 使用，每个 session 只能看到由当前会话所插入的数据，当一个 session 执行 commit 或 rollback 时，由当前 session 所插入的数据将会

自动清除，另外，只有当所有的 session 都断开连接时，临时表才可以被删除

4-6，select u.table_name, u.temporary, u.duration from user_tables u where u.table_name='临时表名';

表示查询临时表的表名，是否是临时表，表的种类（是 session 断开时删除数据，还是事务结束时删除数据）；

三、理解何时需要创建簇表来提高访问速度

当有两个表经常被同时查询时可以创建