《Expert one on one Oracle》- 表- 笔记-2

堆表

平常我们使用的绝大多数的表都是堆表。堆，在数据结构中就是一块大的空间，磁盘或者内存，并以随机的方式管理。所以，查询堆表结果并不是以插入的顺序输出。

SQL> show parameters db_block_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

SQL> create table t
2 ( a int,
3 b varchar2(4000) default rpad('*',4000,'*'),
4 c varchar2(3000) default rpad('*',3000,'*')
5 )
6 /
Table created.

SQL> insert into t (a) values ( 1);
1 row created.

SQL> insert into t (a) values ( 2);
1 row created.

SQL> insert into t (a) values ( 3);
1 row created.

SQL> delete from t where a = 2 ;
1 row deleted.

SQL> insert into t (a) values ( 4);
1 row created.

SQL> SELECT a, DBMS_ROWID.rowid_object (ROWID) OBJECT,
2 DBMS_ROWID.rowid_relative_fno (ROWID) file_no,
3 DBMS_ROWID.rowid_block_number (ROWID) BLOCK,
4 DBMS_ROWID.rowid_row_number (ROWID) rowno
5 FROM t; ;--输出顺序不是134，而是143, 最后插入的块被插入到中间的块

A OBJECT FILE_NO BLOCK ROWNO
---------- ---------- ---------- ---------- ----------
1 30943 12 10 0
4 30943 12 11 1
3 30943 12 12 0

创建表的时候有非常多选项，但是我们通常只需要关心PCTFREE，PCTUSED，INITRANS，FREELISTS。通过如下的方式可以查看创建过程的其他参数：创建的内容在T.SQL文件中。
exp userid=scott/scott tables=t
imp userid=scott/scott full=y indexfile=t.sql

索引组织表

以索引的结构存储数据的表。表的数据就是索引，索引也即数据。相对堆表来讲，索引表需要更高的管理和维护成本。因为索引表的存储是顺序的，所以对于数据的增删改都需要较大的成本。

对于更新相对少的表，可以考虑使用索引表，例如一些代码表等。还有，对于那些物理上希望存储在一块的数据，也可以考虑使用索引表，例如：在父子表关联中，希望子表的多条记录物理上存储在一块。

索引表的选项：

相对堆表，无PCTUSED。因为索引表的数据，由于数据的值已经决定它所在的数据块，而不管这块空余空间的大小。同样的原因，PCTFREE值也只在对象创建和填充索引结构的时使用。

NOCRPRESS，让ORACLE存储索引和值的时候不要压缩。如果为CRPRESS，后面整形参数，表示需要压缩的列的数量。从而使得那些重复的列值不再存储。
下面的例子中，表的owner，type是存在大量重复的。所以，可以采用压缩的方式创建索引表。

如果压缩因子为2，创建完成之后，可能的存储为：
Scott/table
11111
22222
33333
44444
55555
…
99999
如果不压缩：可能存储为：
Scott/table/11111
Scott/table/22222
Scott/table/33333
Scott/table/44444
Scott/table/55555
Scott/table/66666
…
Scott/table/99999

通过如下例子，对比压缩和非压缩所使用的空间。

SQL> create or replace
2 procedure show_iot_space
3 ( p_segname in varchar2 )
4 as
5 l_segname varchar2(30);
6 l_total_blocks number;
7 l_total_bytes number;
8 l_unused_blocks number;
9 l_unused_bytes number;
10 l_LastUsedExtFileId number;
11 l_LastUsedExtBlockId number;
12 l_LAST_USED_BLOCK number;
13 begin
14 select 'SYS_IOT_TOP_' object_id
15 into l_segname
16 from user_objects
17 where object_name = upper(p_segname);
18
19 dbms_space.unused_space
20 ( segment_owner => user,
21 segment_name => l_segname,
22 segment_type => 'INDEX',
23 total_blocks => l_total_blocks,
24 total_bytes => l_total_bytes,
25 unused_blocks => l_unused_blocks,
26 unused_bytes => l_unused_bytes,
27 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
28 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
29 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
30
31 dbms_output.put_line
32 ( 'IOT used ' to_char(l_total_blocks-l_unused_blocks) );
33 end;
34 /
Procedure created.

SQL> create table iot
2 ( owner, object_type, object_id,
3 primary key(owner,object_type,object_id)
4 )
5 organization index
6 NOCOMPRESS
7 as
8 select owner, object_type, object_id from all_objects
9 order by owner, object_type, object_id
10 /
Table created.

SQL>
SQL> set serveroutput on
SQL> exec show_iot_space( 'iot' );
IOT used 96
PL/SQL procedure successfully completed.

SQL> drop table iot;
Table dropped.

SQL> create table iot
2 ( owner, object_type, object_id,
3 primary key(owner,object_type,object_id)
4 )
5 organization index
6 compress 1
7 as
8 select owner, object_type, object_id from all_objects
9 order by owner, object_type, object_id
10 /
Table created.

SQL> exec show_iot_space('iot' );
IOT used 74
PL/SQL procedure successfully completed.

SQL> drop table iot;
Table dropped.

SQL> create table iot
2 ( owner, object_type, object_id,
3 primary key(owner,object_type,object_id)
4 )
5 organization index
6 compress 2
7 as
8 select owner, object_type, object_id from all_objects
9 order by owner, object_type, object_id
10 /
Table created.

SQL> exec show_iot_space( 'iot' );
IOT used 38
PL/SQL procedure successfully completed.

通过如上的例子可以看到，参数COMPRESS可以节省存储空间，减少I/O（通常也会消耗更多的CPU）。

PCTTHRESHOLD, OVERFLOW, and INCLUDING

这三个参数用来确保索引的叶块（实际存储索引数据的块）能够有效的存储数据。
a. OVERFLOW：当行的数据增大的时候，通过OVERFLOW设置另外一个段，可以让行数据OVERFLOW到那个段去。在OVERFLOW中也有PCTUSED，PCTFREE设置，这些都与堆表的相同。
b. PCTTHRESHOLD：当行的数量超过一定数量的时候，将会将行的一部分存到其他地方。对于8K的BLOCK，如果PCTTHRESHOLD为10，那么当行的大小超过800BYTE的时候，行的部分内容将不会存储在这个索引块上了。
c. INCLUDING：INCLUDING指定的列以及前面的列，都存储到索引块中，其他的列存储到溢出块中。

这几个参数是创建IOT的重要参数，可以根据列的访问频率考虑是否需要将其放到OVERFLOW中去，可以根据一块要存储的行数来计算PCCTHRESHOLD。
可以在IOT表上创建索引。由于IOT表的行与堆表的行不同，经常移动。IOT表中的行根据主健的值在索引结构中确定位置，当索引本身的大小和形状改变的时候，位置也会更改。所以，ORACLE采用了逻辑ROWID，ROWID基于IOT的主健。IOT表上的索引效率通常比堆表的索引低。