IOT表和rowid讲解 CUUG

视频：
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博客 ” ，链接：http://blog.itpub.net/500314/viewspace-1067361/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/500314/viewspace-1067361/