ORACLE索引组织表讨论

关于索引组织表本文主要讨论以下几个方面
1、什么是索引组织表
2、索引组织表的关键特性
3、如果建立一个合适的索引组织表
4、什么事逻辑ROWID以及物理猜（Physical Guesses）
5、从内部结构进行分析和证明这些观点

一般的情况下索引是和表分离的SEGMENT，索引的行记录的是索引键值和ROWID，而在索引组织表中就整个表就是一个索引，索引的页节点记录的并非
键值和ROWID而记录是整个数据行，这里和MYSQL INNODB的表非常相像，MYSQL的INNODB 中每一个表都是索引组织表。关于这一点在随后的DUMP中会得
到体现。索引组织表中可以支持各种数据库的特性，比如SECONED INDEX（除主键以外的索引），分区（不能建立子分区），但是不能是聚族表、不能包含
虚拟列，以及LONG COLUMN。
值得注意的是在SECONED INDEX中使用的是逻辑ROWID，而非一般的ROWID，在ORACLE文档中说明了这个逻辑ROWID
Oracle Database uses row identifiers called logical rowids for index-organized tables.
A logical rowid is a base64-encoded representation of the table primary key.
The logical rowid length depends on the primary key length
也就是说这个逻辑ROWID是一个关于主键的base64-encoded的一个值，主键的长度决定了ROWID的长度。为什么引入逻辑ROWID是由于，在堆表中（普通表）中
行的位置是固定，始终会有一个固定的ROWID，即使这行超过整个块允许的长度也会通过行迁移的方式来保留原有的ROWID。而这一点到了索引中就不适用了
索引中的行是可变的，他可能由于某些原因进行了块分裂，那么使用ROWID来标示一行就不那么有用了，这样ORACLE引入了逻辑ROWID，既然行的位置不固定那么
使用主键总是可以的。
下面介绍一下物理猜想（Logical Rowids and Physical Guesses）
一般来说使用逻辑ROWID读取数据的准确性是很高的，不然ORACLE绝不可能使用逻辑ROWID作为SECONED INDEX访问数据的方式，但是在有些情况下，也就是频繁进行
INSERT的表，或者进行大量UPDATE字段为一个很大的值的时候，索引块进行分裂，这样原来的逻辑ROWID就不准了，这个时候如果逻辑ROWID不准确的情况下，就会使用
主键进行查找，利用主键查找的方式一定慢于逻辑ROWID，可能这是由ORACLE的算法决定的不然逻辑ROWID的存在就没有理由了，另外一种更多的情况。

介绍一下索引组织表的溢出段，如果索引组织表中存放的是表所有的数据，那么这个索引结构分裂的可能性大大增加，这样就造成了GUESSES MISS的机率大大增加
我感觉使用溢出段最主要的作用在于降低索引结构分裂造成逻辑ROWID不准的可能性，但是溢出的字段一定是不常用的，或者说不经常查询的，不然整个查询会变得更慢。
所以溢出字段的选择是非常重要的。如果长期都是SELECT * FROM 那么溢出段也就没有用处了。
讨论了这些特性，我们来讨论一下索引组织表的有点，首先显而易见的是索引组织表是可以节约空间的，因为索引和表合二为一，还有就是根据主键进行唯一扫描或者
范围扫描的时候由于索引的排列顺序这些列是按索引排列好的，而且比一般索引少一次ROWID回表的操作，那么速度会更快，第三如果根据数据特点比如一个会员ID，一个
会员卡号，显然一个会员ID可以有多个银行卡号，如果我们建立索引组织表组件为(会员ID和会员卡号），显然如果在查询的时候使用ID=** 那么这种情况下，索引组织
表的优势就出来了，首先他少一次ROWID回表操作，其次索引组织表的排列是有序的，那么同样会员ID的的卡号信息一定存储在临近的块中，这实际也是第二点的一个
列子。

下面首先给出建立索引组织表的部分语法：

create table   ottest1
( empno  number(10) ,
ename  varchar2(10),
city varchar2(20),
state varchar2(2),
zip number(10),
primary key (empno)
)
ORGANIZATION INDEX  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
PCTTHRESHOLD 50 INCLUDING ename
OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS";

ORGANIZATION INDEX 为索引组织表的标示，说明他使用索引组织结构。
PCTFREE、PCTUSED 和一般的没有什么不同，唯一注意的是MSSM和ASSM下堆表的
PCTUSED的不同，因为ASSM下不存在PCTUSED的问题，因为使用位图块进行管理空块的
而索引基本不存在PCTFREE、PCTUSED，使用pctfree只在初始化建立的时候有用。
INITRANS 1 MAXTRANS 255没什么说是初始的事物槽的多少和最大值。
重要就是我们说的
PCTTHRESHOLD 50 INCLUDING "ename" ，这个含义就是刚才溢出段，一个是行占块的百分比一个是哪些列，我们写ename就代表empno和ename就在
索引组织表结构中
OVERFLOW则是我们的溢出段，溢出段可以设置到其他表空间，他就像一个堆表。

下面我们将通过DUMP来看一下索引组织表内部和堆表到底有什么区别：

首先建立2个表一个索引组织表一个普通表，为了观察方便我们插入同样的2条数据
create table   ottest1
( empno  number(10) ,
ename  varchar2(10),
city varchar2(20),
state varchar2(2),
zip number(10),
primary key (empno)
)
ORGANIZATION INDEX  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
PCTTHRESHOLD 50 INCLUDING ename
OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS";

create table notest1
( empno  number(10) ,
ename  varchar2(10),
city varchar2(20),
state varchar2(2),
zip number(10),
primary key (empno)
) ;

insert into ottest1 values(2,'gaopeng','chongqing','CH','400000');
insert into ottest1 values(3,'gaopeng','chongqing','CH','400000');
insert into notest1 values(2,'gaopeng','chongqing','CH','400000');
insert into notest1 values(3,'gaopeng','chongqing','CH','400000');

SQL> select owner,table_name,tablespace_name,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from dba_tables where lower(table_name) in ('notest1','ottest1');
OWNER                          TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS     BLOCKS EMPTY_BLOCKS
------------------------------ ------------------------------ ------------------------------ ---------- ---------- ------------
PTEST                          NOTEST1                        USERS                                   2          5            0
PTEST                          OTTEST1                                                                2

这里可以看到DBA_TABLES中TABLESPACE_NAME为空，因为他的存储信息完全存储在DBA_INDEXES中，而不再TBALES中显示

SQL> select OWNER,INDEX_NAME,INDEX_TYPE,TABLE_NAME,PCT_THRESHOLD,INCLUDE_COLUMN from dba_indexes where lower(table_name) in ('notest1','ottest1');
OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     PCT_THRESHOLD INCLUDE_COLUMN
------------------------------ ------------------------------ --------------------------- ------------------------------ ------------- --------------
PTEST                          SYS_IOT_TOP_79293              IOT - TOP                   OTTEST1                                   50              2
PTEST                          SYS_C0012161                   NORMAL                      NOTEST1

可以看到索引组织表的索引名字为SYS_IOT_TOP_开头为ORACLE自动命名

SQL> select OWNER,SEGMENT_SUBTYPE,HEADER_FILE,HEADER_BLOCK,SEGMENT_NAME,SEGMENT_TYPE from dba_segments where segment_name in ('SYS_IOT_TOP_79293','NOTEST1');
OWNER                          SEGMENT_SUBTYPE HEADER_FILE HEADER_BLOCK SEGMENT_NAME                                                                     SEGMENT_TYPE
------------------------------ --------------- ----------- ------------ -------------------------------------------------------------------------------- ------------------
PTEST                          ASSM                     11          162 NOTEST1                                                                          TABLE
PTEST                          ASSM                     11          154 SYS_IOT_TOP_79293                                                                INDEX

同样我们可以使用，如下方法来导出索引的结构

SQL> select OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where object_name in ('SYS_IOT_TOP_79293','NOTEST1');
OBJECT_NAME                                                                       OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------------------------------------------------------------------------------- ---------- -------------- -------------------
SYS_IOT_TOP_79293                                                                     79295          79295 INDEX
NOTEST1                                                                               79296          79296 TABLE

alter session set events 'immediate trace name treedump level 79295';

----- begin tree dump
leaf: 0x2c0009b 46137499 (0: nrow: 2 rrow: 2)
----- end tree dump

实际这里这里已经找到了页块为0x2c0009b，但是为了简单描述一下ASSM的位图块我们还是从HEAD_BLOCK看起，
并且我们只有1个块的数据，我们可以简单找到第一个数据块就能找到数据，因为IOT表是排序好的。堆表则不同
堆表块的选择基本是随机的（根据PID的hash值判断),所以这种方法不能找到数据在堆表中的块，但是能够简单的
了解一下三级为图块。

SQL> oradebug tracefile_name
/ora11g/diag/rdbms/testdg1/test/trace/test_ora_5288.trc
SQL> alter system dump datafile 11 block 162;

System altered.

SQL> oradebug tracefile_name
/ora11g/diag/rdbms/testdg1/test/trace/test_ora_5292.trc
SQL> alter system dump datafile 11 block 154;

System altered.

：
我们先找到
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1:   0x02c00099
这个LEVEL 2的块然
SQL> select to_number('02c00099','xxxxxxxxxxx') from dual;
TO_NUMBER('02C00099','XXXXXXXX
------------------------------
46137497

SQL>
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
11                            153
然后DUMP这个LEVEL 2的块：
L1 Ranges :
--------------------------------------------------------
0x02c00098  Free: 5 Inst: 1
找到L1块为02c00098

SQL> select dbms_utility.data_block_address_file(46137496),
2     dbms_utility.data_block_address_block(46137496) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
11                            152
再次DUMP L1块

SQL> oradebug tracefile_name

SQL> alter system dump datafile 11 block 152;

System altered.

找到L1的如下信息
Highwater::  0x02c0009c  ext#: 0      blk#: 4      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 1
mapblk  0x00000000  offset: 0
--------------------------------------------------------
DBA Ranges :
--------------------------------------------------------
0x02c00098  Length: 8      Offset: 0

0:Metadata   1:Metadata   2:Metadata   3:25-50% free
4:unformatted   5:unformatted   6:unformatted   7:unformatted
--------------------------------------------------------
End dump data blocks tsn: 4 file#: 11 minblk 152 maxblk 152

根据0:Metadata   1:Metadata   2:Metadata   3:25-50% free我们可以推断
02c00098 02c00099 02c0009a 为metadata块 02c0009b就为数据块为25-50% free
这和我们
----- begin tree dump
leaf: 0x2c0009b 46137499 (0: nrow: 2 rrow: 2)
----- end tree dump
的信息一致。

但是普通表不能通过这个方式获得位置，因为普通表的插入顺序是完全无序的，
这种情况我们通过ROWID进行获取就行了。
最后获得的是
普通表的块的位置是 11,166
索引组织表的块的位置是 11,155

索引组织表的行信息，去掉块头：
row#0[8010] flag: K-----, lock: 2, len=22   \\Row flag set to K
col 0; len 2; (2):  c1 03                   \\主键值
tl: 17 fb: --H-F--- lb: 0x0  cc: 1          \\ H代表是这行的开头 F表示是开始但是没有L 就代表这行的结尾不在这个块中
nrid:  0x02c00096.0                         \\类似行迁移赋予一个指向其他块的指针，.0是行的偏移量,随后我们DUMP这个块
col  0: [ 7]  67 61 6f 70 65 6e 67          \\第一个字段的值
row#1[7988] flag: K-----, lock: 2, len=22
col 0; len 2; (2):  c1 04
tl: 17 fb: --H-F--- lb: 0x0  cc: 1
nrid:  0x02c00096.1
col  0: [ 7]  67 61 6f 70 65 6e 67

索引组织表的溢出段的信息根据02c00096找到为

block_row_dump:
tab 0, row 0, @0x1f85
tl: 19 fb: -----L-- lb: 0x1  cc: 3     \\这里为L就代表这是行的结尾
col  0: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  1: [ 2]  43 48
col  2: [ 2]  c3 29
tab 0, row 1, @0x1f72
tl: 19 fb: -----L-- lb: 0x1  cc: 3
col  0: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  1: [ 2]  43 48
col  2: [ 2]  c3 29

可以看到这里的组织和堆表并无不同。可以考虑为堆表结构，既然是行迁移那么如果要访问这个字段那么必然需要更多的I/O，
这也证明了溢出段为什么如此重要，原因在于为了避免更多的二级索引物理猜失败，而尽可能了保证索引结构的变化更小，
关于二级索引在IOT表块分裂后逻辑ROWID失效的方面将在接下来讨论。

我们还是比较一下普通表的行信息作为对比，去掉块头：
tab 0, row 0, @0x1f7a
tl: 30 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 03
col  1: [ 7]  67 61 6f 70 65 6e 67
col  2: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  3: [ 2]  43 48
col  4: [ 2]  c3 29
tab 0, row 1, @0x1f5c
tl: 30 fb: --H-FL-- lb: 0x1  cc: 5
col  0: [ 2]  c1 04
col  1: [ 7]  67 61 6f 70 65 6e 67
col  2: [ 9]  63 68 6f 6e 67 71 69 6e 67
col  3: [ 2]  43 48
col  4: [ 2]  c3 29

然后我们来看一看所谓的逻辑ROWID的组成，
物理ROWID很简单就是：
如果是AAAC5m AAJ AAABUC AAA
Object: 0x00002e66 = 11878 (AAAC5m)
DBA: 0x02401502 ->
Relative File 9 (AAJ), Block 5378 (AAABUC)
Slot: 0x0000 = 0 (AAA)

32bit的object number，每个数据库最多有4G个对象
10bit的file number，每个对象最多有1022个文件（2个文件预留）
22bit的block number，每个文件最多有4M个BLOCK
16bit的row number，每个BLOCK最多有64K个ROWS

关于逻辑ROWID为，我这里
select dump(rowid,16),a.* from ottest1 a;
为：
2,4,2,c0,0,9b,2,c1,3,fe
2,4,2,c0,0,9b,2,c1,4,fe

按照文档说明如下：
2,4,2,64,18,245,2,193,3,7,120,199,10,1,1,1,1,254

Type Indicator: 2 (Logical ROWID)
DBA Guess Length: 4
DBA Guess: 02,64,18,24 = 0x024012f5
(File 9, Block 4853)
Slot Guess Length: 2
Slot Guess: 193,3 (Slot 2)
Primary Key Length: 7
Primary Key Value: 120,199,10,1,1,1,1
(01-OCT-2099)

可以对照但是我这里并没有Slot Guess: 193,3 (Slot 2)
可以看出10G-11G逻辑ROWID组成为
Type Indicator+DBA Guess Length+DBA Guess+Slot Guess Length+Primary Key Length+Primary Key Value
既然最后是主键值那么也就是反证了最开始给出了为什么受到主键长度的影响逻辑ROWID会变长。

那么二级索引中又记录了什么样的值呢？
为此我建立了如下的索引
create index ottest1_n_i on ottest1(ename);

如下：
row#0[8011] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
row#1[7990] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
----- end of leaf block dump -----

可以看到col  0: [ 4]  02 c0 00 9b就是DBA的值，而col 1; len 2; (2):  c1 03是我们的主键
这样可以看到当块分裂后DBA的值改变了其中记录的DBA Guess也就不准确了。

我们进行大量的数据插入后在来看看我们的这个逻辑ROWID分裂块后是否还是准确的。

IOT表中索引的结构已经发生了变化

branch: 0x2c0009b 46137499 (0: nrow: 32, level: 1)
leaf: 0x2c0009c 46137500 (-1: nrow: 323 rrow: 323)
leaf: 0x2c0009d 46137501 (0: nrow: 319 rrow: 319)
leaf: 0x2c0009e 46137502 (1: nrow: 319 rrow: 319)
leaf: 0x2c0009f 46137503 (2: nrow: 319 rrow: 319)
leaf: 0x2c000dc 46137564 (3: nrow: 320 rrow: 320)
leaf: 0x2c000dd 46137565 (4: nrow: 319 rrow: 319)
leaf: 0x2c000de 46137566 (5: nrow: 319 rrow: 319)
leaf: 0x2c000df 46137567 (6: nrow: 319 rrow: 319)
......

可以看到原有2c0009b块已经成了跟节点。我们知道跟节点是不存放的数据的。而原有的数据已经变成了
SQL> select dump(rowid,16) from ottest1 where empno=2;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=208 Len=10: 2,4,2,c0,0,9c,2,c1,3,fe
这里是我们的以前的数据 主键ID为2的行DBA已经变成
2,c0,0,9c和DUMP一致而以前是
2,c0,0,9b,但是2,c0,0,9b这个块现在已经是分支节点了，说明他的位置已经发生了改变而且是块级别的改变。
这说明随着IOT的表的变大索引的块会不断分离，物理位置也在不断的改变。

我们再来DUMP一下原有的索引块而且还是 2 和3 这两条记录
row#0[719] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
row#1[740] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9b
可以看到col  0: [ 4]  02 c0 00 9b和以前的一样并没有跟新为02 c0 00 9c，这也充分的说明
索引块的分裂会导致原有的二级索引的逻辑ROWID错误，导致一次使用主键去访问数据。
当然如果我们进行索引REBULID后我们再来看看

SQL> alter index ottest1_n_i rebuild online;
Index altered

row#0[719] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 03
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9c
row#1[740] flag: K-----, lock: 0, len=21
col 0; len 7; (7):  67 61 6f 70 65 6e 67
col 1; len 2; (2):  c1 04
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 4]  02 c0 00 9c

最后我们发现如果要纠正逻辑ROWID错误的方法就是REBUILD二级索引，但是有必要说一下，这种情况仅仅是
二级索引会出现问题，但是主键不会有任何问题。

所以最后给出索引组织表不适合的情况
有大量的二级索引（非主键），如果频繁INSERT操作，会导致二级索引逻辑ROWID错误，造成GUESS失败，这种情况
下ORACLE不得不用主键来进行定位操作，这个代价是显而易见的因为仅仅有主键ORACLE不得不重新扫描IOT表的主键来得到数据，
那么很显然他要经历两个索引结构的遍历1次是二级索引，一次是索引组织表本生的索引，如果逻辑ROWID不出问题，那么就只是经历
一次索引结构的遍历，根据GUESS DBA就能定位到块。虽然可以进行REBULID来纠正这个问题但是在大型的系统大型的表中REBULID
是那么困难。
另外就是索引组织表不支持的方面比如子分区。
当然如果一个表只有一个主键，而基本没有什么二级索引并且为了节约空间，
，且长期按照这个主键来进行访问，使用索引组织表是可以的。