IOT, Secondary Index and Mapping Table

本文尝试介绍下IOT(Index Organization Table)相关的secondary index 和 mapping table的基本知识,这个也是我以前一直没有搞明白的东西。

1. IOT (Index Organization Table)



关于IOT本身来说没有什么好说的,IOT是跟常见的Heap table相对的,可以简单理解成“表即是索引,索引就是表”。 跟heap表相比起来,存储空间减少了,因为不需要给表还有索引都分配空间,其次基于primary key的数据访问效率更高,因为不需要table access by index rowid的操作了。


2. IOT and Secondary Index



所谓的secondary index自然是跟primary key index想对而言的,因为IOT就是根据primary key创建起来的索引结构。primary key不可能总是cover所有的列,如果想像heap表那样快速访问非primary key的列,自然是想在这些列上建单独的索引。这样的索引在IOT上就称之为secondary index, 跟primary (key) index相对而言。 

但是这个secondary key并不能像heap表上的索引那样,每个索引叶子节点包含索引列的内容和指向对应表中行的物理 (physical) rowid。 这是因为IOT组织方式跟heap表不一样,IOT的每一行的物理位置(rowid)不会一成不变,如果有数据插入的话,因为IOT是按照Primary排序的,因此数据行在IOT的索引树中的物理位置很有可能就会发生变化。因此如果secondary index也像普通heap表的索引那样存储一个对应数据行的physical rowid自然是不行的。那么怎么办呢,因为primary key是相对不变的,因此secondary index中引入了所谓的逻辑(logical) rowid的概念,所谓的logical rowid可以简单理解成就是primary key, 除了加入了logical rowid之外呢,还有个所谓的physical guess, 我觉得可以就理解成physical rowid, 因为有可能IOT中的数据行的physical rowid没有改变,那么用这个physical guess不就快多了嘛!如果这个physical guess被证实是”无效的“,那么好吧,还是用回logical rowid (primary key)来通过IOT的primary key来查找数据,这就相当于"full table scan"了。因此,IOT上的secondary index相对来说,效率就不是那么好了。
因此secondary index的叶子节点的每个entry一般包含 (1) 索引列的数据 (2) logical rowid (based on primary key) (3)physical guess (也可能不存在) 
Jonathan Lewis‘ view on secondary indexes

 

I think secondary indexes on IOTs need some careful testing. It's probably not an area that many people have used in a high-stress environment. There are two main issues:

  • The primary key is used in the secondary index instead of a rowid so for large primary keys, the index would be bigger than the equivalent index on a simple table.
  • The secondary index holds a 'guess' block address for the row it points to so that a query can go to the right block more cheaply. But if the row has moved (e.g. leaf block split) then the guess is wrong and is a cost, not a benefit. But this won't be a problem if your application is always adding data at the 'right-hand' edge of the index.


可以通过查询视图user_indexes中的PCT_DIRECT_ACCESS来查看secondary index的physical guess的命中情况, 如果命中率很低的话,可以通过重建该secondary index来改进。 

SELECT index_name, index_type, pct_direct_access
FROM user_indexes
WHERE pct_direct_access IS NOT NULL;

 

现在来想一个问题,既然logical rowid是依赖于primary key的,如果primary key更新的话,那么会发生什么情况呢? secondary index还会有用吗?index entry会更新吗? 做个简单实验吧!!
SQL> create table test_iot
2 (id, name, primary key(id))
3 organization index
4 as
5 select rownum, object_name from user_objects;

Table created.
SQL> select * from test_iot;

ID NAME
---------- ------------------------------
1 TEST_IOT
2 TEST_QUEUE_CALLBACK_PROCEDURE
3 MESSAGE_API
4 MESSAGE_API
5 SYS_C0054672
6 TEST_MSG_TYPE
7 SYS_LOB0000262459C00030$$
8 AQ$_TEST_QUEUE_TABLE_S
9 SYS_C0054675
10 AQ$_TEST_QUEUE_TABLE_V
11 AQ$_TEST_QUEUE_TABLE_T
12 AQ$TEST_QUEUE_TABLE_S
13 AQ$_TEST_QUEUE_TABLE_N
14 SYS_IOT_TOP_262468
15 TEST_QUEUE_TABLE
16 AQ$_TEST_QUEUE_TABLE_H
17 SYS_IOT_TOP_262470
18 SYS_IOT_OVER_262472
19 AQ$_TEST_QUEUE_TABLE_G
20 SYS_IOT_TOP_262472
21 AQ$_TEST_QUEUE_TABLE_I
22 SYS_IOT_TOP_262475
23 AQ$_TEST_QUEUE_TABLE_E
24 AQ$_TEST_QUEUE_TABLE_F
25 AQ$TEST_QUEUE_TABLE
26 TEST_QUEUE_R
27 TEST_QUEUE_N
28 AQ$TEST_QUEUE_TABLE_R
29 TEST_QUEUE
30 TEST_MESSAGE_TABLE

30 rows selected.

 

很奇怪,记录 'TEST_IOT' 居然也包含在表TEST_IOT中。 从这个结果可以推断出CTAS (create table as select) 应该是分两步来完成,首先还是先创建表,然后往表里插入数据。


现在来创建一个二级索引,

SQL> create index idx_test_iot_1 on test_iot(name);

Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=> 'TEST_IOT', cascade=>true);

PL
/SQL procedure successfully completed.

 

SQL> select * from test_iot
2 where name = 'TEST_IOT';


Execution
Plan
----------------------------------------------------------
Plan hash value: 471874511

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST_IOT_1 | 1 | 21 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified
by operation id):
---------------------------------------------------

1 - access("NAME"='TEST_IOT')

 

注意这个执行计划,很显然是用到了name上的index - idx_test_iot_1, 但是总是觉得奇怪,为啥没有用到基于主键的”全表“扫描呢?毕竟我的select语句可用的是select *, 而不是select name啊。不过仔细想想这个也是合理的,同时也证明了secondary index包含了主键,因为表TEST_IOT只包含两列,primary key  + NAME! 

 

下面来换个3列的IOT看看怎么个情况...

 

SQL> create table TEST_IOT_3_COLS
2 (ID, NAME, NAME2, constraint pk_test_iot_3_col primary key(id))
3 organization index
4 as
5 select object_id, object_name, object_name
6 from user_objects;

Table created.

 

SQL> create index idx_test_iot_3_col on test_iot_3_cols(name);

Index created.

 

SQL> select * from test_iot_3_cols where name='TEST_IOT_3_COLS';


Execution
Plan
----------------------------------------------------------
Plan hash value: 4173456250

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_TEST_IOT_3_COL | 1 | 42 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| IDX_TEST_IOT_3_COL | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified
by operation id):
---------------------------------------------------

1 - access("NAME"='TEST_IOT_3_COLS')
2 - access("NAME"='TEST_IOT_3_COLS')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
548 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

 

从这个执行计划不难看出,除了用到secondary index 还用到了基于primary key 的 “全表” 扫描。 注意这个“全表”指的就是IOT的索引组织。

 

 

本来以为更改primary key会导致执行计划改变,出现我想像中的类似"full table scan"的操作,不过想想这个是IOT, 基于主键的INDEX UNIQUE SCAN其实就包含了这个"full table scan"。 如下面的操作显示,

SQL> update test_iot_3_cols set id = 1 where name='TEST_IOT_3_COLS';

1 row updated.

SQL
> commit;
SQL> select * from test_iot_3_cols where name = 'TEST_IOT_3_COLS';


Execution
Plan
----------------------------------------------------------
Plan hash value: 4173456250

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_TEST_IOT_3_COL | 1 | 42 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| IDX_TEST_IOT_3_COL | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

 

虽然从执行计划上看不出来什么变化,不过可以想见的是这个时候'physical guess'应该是失效了。 (这个待进一步验证)

 

 

3. IOT and Mapping Table



关于IOT还有个问题就是所谓的Mapping Table, 既然是mapping table 自然要问是 what VS what的一个mapping呢? 

Mapping table并不是必需的,只有在IOT上创建bitmap 类型的secondary index的时候才需要,如下面的操作所示...

SQL> create bitmap index btidx_test_iot on test_iot(name);
create bitmap index btidx_test_iot on test_iot(name)
*
ERROR at line
1:
ORA
-28669: bitmap index can not be created on an IOT with no mapping table


注意到Mapping table是个heap table, 用来存储IOT table的logical rowid (primary key)。 Mapping table的每一行存储了对应的IOT表中记录的logical rowid. 因此这个mapping table就维护了IOT 表logical rowid 和 mapping table的每一行的physical rowid 的mapping 的关系。为什么要这样做呢?因为bitmap索引条目中保存的rowid要用physical rowid, 而IOT是无法提供稳定的physical rowid的,因此就借助于mapping table的physical rowid。 通过bitmap索引来访问表中的数据的执行计划大概就是首先根据bitmap index中的physical rowid访问mapping table, 然后通过mapping table中的logical rowid再来访问IOT表。


注意一个IOT只需要一个mapping table, 所有的bitmap index共用这一个mapping table. 


下面来简单做个测试...


SQL> create table my_iot
2 ( x integer, y integer, constraint pk_my_iot primary key(x))
3 organization index
4 mapping table;

Table created.

SQL
> col iot_map_table new_val iot_map
SQL
> select 'SYS_IOT_MAP_' || object_id iot_map_table
2 from user_objects
3 where object_name='MY_IOT';

IOT_MAP_TABLE
----------------------------------------------------
SYS_IOT_MAP_262822

 

Mapping table的命名方式为SYS_IOT_MAP_<IOT的Object id>, 这个例子显示MY_IOT的mapping table的名字叫SYS_IOT_MAP_262822.



SQL> desc &iot_map;
Name
Null? Type
-------------------------------------------
SYS_NC_01 ROWID

可以看到mapping table里面有一列类型为ROWID的字段,用来保存logical rowid. 



SQL> select * from &iot_map;
old
1: select * from &iot_map
new
1: select * from SYS_IOT_MAP_262822

no rows selected

 

SQL> insert into my_iot values (1, 2);

1 row created.

SQL
> insert into my_iot values (2, 2);

1 row created.

SQL
> commit;

Commit complete.


SQL
> col sys_nc_01 for a30
SQL
> select rowid, a.* from &iot_map a;
old
1: select rowid, a.* from &iot_map a
new
1: select rowid, a.* from SYS_IOT_MAP_262822 a

ROWID SYS_NC_01
------------------ ------------------------------
AABAKnAAEAAACV+AAA *BAAAAAACwQL+
AABAKnAAEAAACV
+AAB *BAAAAAACwQP+

SQL
>

 

可以看出logical rowid和pyhsical rowid的存储方式还是有很大区别的。

虽然可以直接访问mapping table, 但是直接对表中的数据进行修改是不允许的,


SQL> delete from &iot_map;
old
1: delete from &iot_map
new
1: delete from SYS_IOT_MAP_262822
delete from SYS_IOT_MAP_262822
*
ERROR at line
1:
ORA
-28668: cannot reference mapping table of an index-organized table

 

尝试修改IOT表中的主键,看看mapping table中的数据有何变化,


SQL> update my_iot set x=3 where x=1;

1 row updated.

SQL
> select rowid, a.* from &iot_map a;
old
1: select rowid, a.* from &iot_map a
new
1: select rowid, a.* from SYS_IOT_MAP_262822 a

ROWID SYS_NC_01
------------------ ------------------------------
AABAKnAAEAAACV+AAA *BAEAJYQCwQT+
AABAKnAAEAAACV
+AAB *BAAAAAACwQP+

SQL
>

 

可以看到logical rowid发生了变化。这也回答了在第二部分提出的问题, 如果更改了primary key, secondary index中的logical rowid会同时更新。

如果更改非primary key呢?可以想见logical rowid应该不会改的,因为logical rowid 只跟primary key 相关。 

SQL> update my_iot set y=3 where x = 3;

1 row updated.

SQL
> select rowid, a.* from &iot_map a;
old
1: select rowid, a.* from &iot_map a
new
1: select rowid, a.* from SYS_IOT_MAP_262822 a

ROWID SYS_NC_01
------------------ ------------------------------
AABAKnAAEAAACV+AAA *BAEAJYQCwQT+
AABAKnAAEAAACV
+AAB *BAAAAAACwQP+

SQL
>

 

 

现在给表my_iot的列y上创建一个bitmap index, 

SQL> create bitmap index bit_idx_my_iot on my_iot(y);

Index created.

SQL
> select * from my_iot;

X Y
---------- ----------
2 2
3 3

SQL
>

 

 

现在来看看根据bitmap索引来查找数据的执行计划,

SQL> select * from my_iot where y=2;


Execution
Plan
----------------------------------------------------------
Plan hash value: 2571294585

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 1 (0)| 00:00:01 |
| 1 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 2 | BITMAP INDEX SINGLE VALUE | BIT_IDX_MY_IOT | | | | |
----------------------------------------------------------------------------------------------

Predicate Information (identified
by operation id):
---------------------------------------------------

2 - access("Y"=2)

 

注意到执行计划里面没有通过MY_IOT表的primary key访问的操作。不过根据前面的经验,my_iot同样是个两列的表 ,因为mapping table里面包含了logical rowid, 不需要再访问IOT了。


现在给表my_iot增加一列,

 

SQL> alter table my_iot add (z integer);

Table altered.

 

SQL> select /*+ index(t bit_idx_my_iot) */ * from my_iot t where y=2;


Execution
Plan
----------------------------------------------------------
Plan hash value: 2515803521

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 51 (0)| 00:00:01 |
| 1 | INDEX UNIQUE SCAN | PK_MY_IOT | 1 | 39 | 51 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | BIT_IDX_MY_IOT | | | | |
-----------------------------------------------------------------------------------------------

Predicate Information (identified
by operation id):
---------------------------------------------------

3 - access("Y"=2)

 

注意我用了一个hint强制优化器使用bitmap index, 这样显示的执行计划才是通过IOT表上bitmap index访问表的典型执行计划, 注意ID为1的操作 (index unique scan)。 




posted @ 2010-11-10 20:27  FangwenYu  阅读(1467)  评论(0编辑  收藏  举报