IOT, Secondary Index and 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相对而言。
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')
下面来换个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)。
--------------------------------------
Regards,
FangwenYu