视频：
http://v.youku.com/v_show/id_XNjAxMDE4MTUy.html
索引组织表
IOT
索引： 根--枝--叶
根块：指向枝块的指针
枝块：指向叶块的指针
叶块：索引块的值
rowid就是唯一标志记录物理位置的一个id
SQL> select ROWID from Bruce_test where rownum<2;
ROWID
------------------ ----------
AAABnlAAFAAAAAPAAA
ROWID的格式如下：
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
我们可以看出，从上面的rowid可以得知：
AAABnl 是数据对象编号
AAF是相关文件编号
AAAAAP是块编号
AAA 是行编号
怎么依据这些编号得到具体的十进制的编码值呢，这是经常遇到的问题。这里需要明白rowid的是基于64位编码的18个字符显示（数据对象编号(6) +文件编号(3) +块编号(6)+ 行编号(3)=18位），其中
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
共64位，明白这个后，就可以计算出10进制的编码值，计算公式如下：
d * (b ^ p)
其中：b就是基数，这里就是64，p就是从右到左，已0开始的位置数
比如：上面的例子
文件号AAF,具体的计算应该是:
5*(64^0)=5；
0*(64^1)=0；
0*(64^2)=0；
文件号就是0+0+5=5
最简单的基于rowid的显示方式得到的响应的64位编码对应值的sql：
select rowid ,
substr(rowid,1,6) "OBJECT",
substr(rowid,7,3) "FILE",
substr(rowid,10,6) "BLOCK",
substr(rowid,16,3) "ROW"
from TableName;
OWID OBJECT FILE BLOCK ROW
------------------ ------------ ------ ------------ ------
AAABc4AADAAAGLUAAA AAABc4 AAD AAAGLU AAA
AAABc4AADAAAGLUAAB AAABc4 AAD AAAGLU AAB
AAABc4AADAAAGLUAAC AAABc4 AAD AAAGLU AAC
AAABc4AADAAAGLUAAD AAABc4 AAD AAAGLU AAD
AAABc4AADAAAGLUAAE AAABc4 AAD AAAGLU AAE
通过dbms_rowid这个包，可以直接的得到具体的rowid包含的信息：
select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from bruce_t where rownum<5;
OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
5944 3 25300 0
5944 3 25300 1
5944 3 25300 2
5944 3 25300 3
另外，就是自己写的一些函数：（下面的函数是网友eygle提供）
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/
应用上面的函数如下：
SQL> select get_rowid(rowid), name from bruce_t;
GET_ROWID(ROWID) NAME
-------------------------------------------------------------------------------- --------------------------------
Object# is :5944 BruceLau
Relative_fno is :3
Block number is :25300
Row number is :0
Object# is :5944 MabelTang
Relative_fno is :3
Block number is :25300
Row number is :1
IOT的基表的数据存放在索引块中。
索引组织表和堆表
索引组织表和堆表的对比:
快速的主键访问数据
节省存储空间
辅助索引和逻辑行id
有以下限制:
– 必须有一个主键Must have a primary key
– 不能使用唯一性约束
– 不能使用簇
IOT适用的场合有：
1、完全由主键组成的表。这样的表如果采用堆组织表，则表本身完全是多余的开销，因为所有的数据全部同样也保存在索引里，此时，堆表是没用的。
2、代码查找表。如果你只会通过一个主键来访问一个表，这个表就非常适合实现为IOT.
3、如果你想保证数据存储在某个位置上，或者希望数据以某种特定的顺序物理存储，IOT就是一种合适的结构。
1.必须有主键
2.访问的时候通畅以这个主键列排序方式访问
3.主键列很少被修改
索引组织表的应用
Heap Table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。
IOT 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。
适用于信息检索、空间和OLAP程序。
索引组织表的适用情况：
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位，要数据按特定顺序物理存储。
5、经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。
经常更新的表当然不适合IOT，因为oracle需要不断维护索引，而且由于字段多索引成本就大。
如果不是经常使用主键访问表，就不要使用IOT
IOT行溢出
普通索引块能够包含多个索引行
IOT 一个索引块不能存放太多的索引行
PCTTHRESHOLD 来解决
如果索引块的空间占
PCTTHRESHOLD n ：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段
当某一行所占的存储空间超过快空间20%，那么我们可以把某些列（除了主键列）放在在另外的一个地方，溢出的数据叫溢出段；
一般会把不经常访问的列放入溢出端
INCLUDING column_name ：指定列之前的列都放入索引块，之后的列都放到溢出段
● 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。
● 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。
SQL> CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL,
country_name VARCHAR2(40),
currency_name VARCHAR2(25),
currency_symbol VARCHAR2(3),
map BLOB,
flag BLOB,
CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id))
ORGANIZATION INDEX
PCTTHRESHOLD 20
OVERFLOW TABLESPACE USER
INCLUDING name ;
使用mapping表
SQL> CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, currency_name VARCHAR2(25)
, currency_symbol VARCHAR2(3)
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id))
ORGANIZATION INDEX
MAPPING TABLE TABLESPACE USERS;
SQL> CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, currency_name VARCHAR2(25)
, currency_symbol VARCHAR2(3)
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id))
ORGANIZATION INDEX
MAPPING TABLE TABLESPACE USERS;
维护Mapping表
SQL> SELECT INDEX_NAME, PCT_DIRECT_ACCESS
2 FROM DBA_INDEXES
3 WHERE pct_direct_access is not null;
SQL> SELECT INDEX_NAME, PCT_DIRECT_ACCESS
2 FROM DBA_INDEXES
3 WHERE pct_direct_access is not null;
通过分析IOT 表来收集mapping表统计**.
通过查询DBA_INDEXES 视图来获取mapping表的准
确的使用情况.
使用ALTER TABLE 重建mapping表.
把物理的rowid和逻辑ROWID做映射。mapping表没有实际的数据，只是做物理ROWID的映射而已
索引组织表(IOT)数据字典
SQL> select table_name,tablespace_name,iot_name,iot_type
2 from DBA_TABLES;
TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE
----------------- --------------- -------- ------------
COUNTRIES IOT
SYS_IOT_OVER_2268 USER_DATA COUNTRIES IOT_OVERFLOW
SQL> select index_name,index_type,tablespace_name,table_name
2 from DBA_INDEXES;
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME
----------------- ---------- ---------- ----------
COUNTRIES_C_ID_PK IOT - TOP INDX COUNTRIES
SQL> select index_name,index_type,tablespace_name,table_name
2 from DBA_INDEXES;
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME
----------------- ---------- ---------- ----------
COUNTRIES_C_ID_PK IOT - TOP INDX COUNTRIES
SQL> select segment_name,tablespace_name,segment_type
2 from DBA_SEGMENTS;
SEGMENT_NAME TABLESPACE SEGMENT_TYPE
----------------- ---------- ------------
SYS_IOT_OVER_2268 DATA TABLE
COUNTRIES_C_ID_PK INDX INDEX
SQL> select segment_name,tablespace_name,segment_type
2 from DBA_SEGMENTS;
SEGMENT_NAME TABLESPACE SEGMENT_TYPE
----------------- ---------- ------------
SYS_IOT_OVER_2268 DATA TABLE
COUNTRIES_C_ID_PK INDX INDEX
1、IOT 表的另外一种用法是针对多对多（many to many）关系的表结构，比如：
学生管理系统中，需要包含学生编号、姓名、年龄、课程编号、课程名称、教师编号，成绩等信息。
1、在表的设计中，要保证学生编号和课程编号是主键约束，如果把他们都放在一张表，就会出现多对多的关系，比如：
学生编号
课程编
号
姓名
。。。。
101
101
1001
1002
.....
因为学生会学习多个课程，这样子学生编号就会有多个，违反了唯一键约束。
解决办法就是学生编号和课程编号加个混合主键约束，这样子就能够解决违反唯一约束的问题，但是这样子学生名字、年龄、
课程名称、教师编号等列就会出现大量的冗余。
************************************
如何解决唯一约束和冗余的问题呢，很简单，弄 3 张表，比如：
1、学生表
学生编号
2、课程表
姓名
年龄
课程编号
课程名称
教师编号
3、关联表（IOT），为了解决唯一约束，可以在学生编号、课程编号两个列上建立多列主键约束
学生编号
课程编号
成绩
http://v.youku.com/v_show/id_XNjAxMDE4MTUy.html
索引组织表
IOT
索引： 根--枝--叶
根块：指向枝块的指针
枝块：指向叶块的指针
叶块：索引块的值
rowid就是唯一标志记录物理位置的一个id
SQL> select ROWID from Bruce_test where rownum<2;
ROWID
------------------ ----------
AAABnlAAFAAAAAPAAA
ROWID的格式如下：
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
我们可以看出，从上面的rowid可以得知：
AAABnl 是数据对象编号
AAF是相关文件编号
AAAAAP是块编号
AAA 是行编号
怎么依据这些编号得到具体的十进制的编码值呢，这是经常遇到的问题。这里需要明白rowid的是基于64位编码的18个字符显示（数据对象编号(6) +文件编号(3) +块编号(6)+ 行编号(3)=18位），其中
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
共64位，明白这个后，就可以计算出10进制的编码值，计算公式如下：
d * (b ^ p)
其中：b就是基数，这里就是64，p就是从右到左，已0开始的位置数
比如：上面的例子
文件号AAF,具体的计算应该是:
5*(64^0)=5；
0*(64^1)=0；
0*(64^2)=0；
文件号就是0+0+5=5
最简单的基于rowid的显示方式得到的响应的64位编码对应值的sql：
select rowid ,
substr(rowid,1,6) "OBJECT",
substr(rowid,7,3) "FILE",
substr(rowid,10,6) "BLOCK",
substr(rowid,16,3) "ROW"
from TableName;
OWID OBJECT FILE BLOCK ROW
------------------ ------------ ------ ------------ ------
AAABc4AADAAAGLUAAA AAABc4 AAD AAAGLU AAA
AAABc4AADAAAGLUAAB AAABc4 AAD AAAGLU AAB
AAABc4AADAAAGLUAAC AAABc4 AAD AAAGLU AAC
AAABc4AADAAAGLUAAD AAABc4 AAD AAAGLU AAD
AAABc4AADAAAGLUAAE AAABc4 AAD AAAGLU AAE
通过dbms_rowid这个包，可以直接的得到具体的rowid包含的信息：
select dbms_rowid.rowid_object(rowid) object_id, dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,dbms_rowid.rowid_row_number(rowid) num from bruce_t where rownum<5;
OBJECT_ID FILE_ID BLOCK_ID NUM
---------- ---------- ---------- ----------
5944 3 25300 0
5944 3 25300 1
5944 3 25300 2
5944 3 25300 3
另外，就是自己写的一些函数：（下面的函数是网友eygle提供）
create or replace function get_rowid
(l_rowid in varchar2)
return varchar2
is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number);
ls_my_rowid := 'Object# is :'||to_char(object_number)||chr(10)||
'Relative_fno is :'||to_char(relative_fno)||chr(10)||
'Block number is :'||to_char(block_number)||chr(10)||
'Row number is :'||to_char(row_number);
return ls_my_rowid ;
end;
/
应用上面的函数如下：
SQL> select get_rowid(rowid), name from bruce_t;
GET_ROWID(ROWID) NAME
-------------------------------------------------------------------------------- --------------------------------
Object# is :5944 BruceLau
Relative_fno is :3
Block number is :25300
Row number is :0
Object# is :5944 MabelTang
Relative_fno is :3
Block number is :25300
Row number is :1
IOT的基表的数据存放在索引块中。
索引组织表和堆表
索引组织表和堆表的对比:
快速的主键访问数据
节省存储空间
辅助索引和逻辑行id
有以下限制:
– 必须有一个主键Must have a primary key
– 不能使用唯一性约束
– 不能使用簇
IOT适用的场合有：
1、完全由主键组成的表。这样的表如果采用堆组织表，则表本身完全是多余的开销，因为所有的数据全部同样也保存在索引里，此时，堆表是没用的。
2、代码查找表。如果你只会通过一个主键来访问一个表，这个表就非常适合实现为IOT.
3、如果你想保证数据存储在某个位置上，或者希望数据以某种特定的顺序物理存储，IOT就是一种合适的结构。
1.必须有主键
2.访问的时候通畅以这个主键列排序方式访问
3.主键列很少被修改
索引组织表的应用
Heap Table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定。
IOT 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。
适用于信息检索、空间和OLAP程序。
索引组织表的适用情况：
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位，要数据按特定顺序物理存储。
5、经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。
经常更新的表当然不适合IOT，因为oracle需要不断维护索引，而且由于字段多索引成本就大。
如果不是经常使用主键访问表，就不要使用IOT
IOT行溢出
普通索引块能够包含多个索引行
IOT 一个索引块不能存放太多的索引行
PCTTHRESHOLD 来解决
如果索引块的空间占
PCTTHRESHOLD n ：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段
当某一行所占的存储空间超过快空间20%，那么我们可以把某些列（除了主键列）放在在另外的一个地方，溢出的数据叫溢出段；
一般会把不经常访问的列放入溢出端
INCLUDING column_name ：指定列之前的列都放入索引块，之后的列都放到溢出段
● 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。
● 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。
SQL> CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL,
country_name VARCHAR2(40),
currency_name VARCHAR2(25),
currency_symbol VARCHAR2(3),
map BLOB,
flag BLOB,
CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id))
ORGANIZATION INDEX
PCTTHRESHOLD 20
OVERFLOW TABLESPACE USER
INCLUDING name ;
使用mapping表
SQL> CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, currency_name VARCHAR2(25)
, currency_symbol VARCHAR2(3)
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id))
ORGANIZATION INDEX
MAPPING TABLE TABLESPACE USERS;
SQL> CREATE TABLE countries
( country_id CHAR(2)
CONSTRAINT country_id_nn NOT NULL
, country_name VARCHAR2(40)
, currency_name VARCHAR2(25)
, currency_symbol VARCHAR2(3)
, CONSTRAINT country_c_id_pk
PRIMARY KEY (country_id))
ORGANIZATION INDEX
MAPPING TABLE TABLESPACE USERS;
维护Mapping表
SQL> SELECT INDEX_NAME, PCT_DIRECT_ACCESS
2 FROM DBA_INDEXES
3 WHERE pct_direct_access is not null;
SQL> SELECT INDEX_NAME, PCT_DIRECT_ACCESS
2 FROM DBA_INDEXES
3 WHERE pct_direct_access is not null;
通过分析IOT 表来收集mapping表统计**.
通过查询DBA_INDEXES 视图来获取mapping表的准
确的使用情况.
使用ALTER TABLE 重建mapping表.
把物理的rowid和逻辑ROWID做映射。mapping表没有实际的数据，只是做物理ROWID的映射而已
索引组织表(IOT)数据字典
SQL> select table_name,tablespace_name,iot_name,iot_type
2 from DBA_TABLES;
TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE
----------------- --------------- -------- ------------
COUNTRIES IOT
SYS_IOT_OVER_2268 USER_DATA COUNTRIES IOT_OVERFLOW
SQL> select index_name,index_type,tablespace_name,table_name
2 from DBA_INDEXES;
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME
----------------- ---------- ---------- ----------
COUNTRIES_C_ID_PK IOT - TOP INDX COUNTRIES
SQL> select index_name,index_type,tablespace_name,table_name
2 from DBA_INDEXES;
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_NAME
----------------- ---------- ---------- ----------
COUNTRIES_C_ID_PK IOT - TOP INDX COUNTRIES
SQL> select segment_name,tablespace_name,segment_type
2 from DBA_SEGMENTS;
SEGMENT_NAME TABLESPACE SEGMENT_TYPE
----------------- ---------- ------------
SYS_IOT_OVER_2268 DATA TABLE
COUNTRIES_C_ID_PK INDX INDEX
SQL> select segment_name,tablespace_name,segment_type
2 from DBA_SEGMENTS;
SEGMENT_NAME TABLESPACE SEGMENT_TYPE
----------------- ---------- ------------
SYS_IOT_OVER_2268 DATA TABLE
COUNTRIES_C_ID_PK INDX INDEX
1、IOT 表的另外一种用法是针对多对多（many to many）关系的表结构，比如：
学生管理系统中，需要包含学生编号、姓名、年龄、课程编号、课程名称、教师编号，成绩等信息。
1、在表的设计中，要保证学生编号和课程编号是主键约束，如果把他们都放在一张表，就会出现多对多的关系，比如：
学生编号
课程编
号
姓名
。。。。
101
101
1001
1002
.....
因为学生会学习多个课程，这样子学生编号就会有多个，违反了唯一键约束。
解决办法就是学生编号和课程编号加个混合主键约束，这样子就能够解决违反唯一约束的问题，但是这样子学生名字、年龄、
课程名称、教师编号等列就会出现大量的冗余。
************************************
如何解决唯一约束和冗余的问题呢，很简单，弄 3 张表，比如：
1、学生表
学生编号
2、课程表
姓名
年龄
课程编号
课程名称
教师编号
3、关联表（IOT），为了解决唯一约束，可以在学生编号、课程编号两个列上建立多列主键约束
学生编号
课程编号
成绩
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/500314/viewspace-1067361/ ，如需转载，请注明出处，否则将追究法律责任。
下一篇：
CUUG笔记 ORACLE索引学习笔记
请登录后发表评论
登录
全部评论
