索引组织表 IOT (ORACLE)

Jimmy Booker 2012-04-18

展开全文

索引组织表 IOT (ORACLE)

2011年06月12日星期日 22:08

索引组织表其实就是存储在一个索引结构中的表

一般我们在oracle数据库中，用到的都是堆组织表，而在mysql常用引擎innodb中的表就是索引组织表，今天因为业务需要，测试了下oracle的索引组织表

需求是这样的，将有几十亿数据的A表的新字段cc更新为一个新值（蛋疼的需求），这个值来自数据仓库统计得到的一个B表，A.ID与B.ID关联，需要将B表拉到A表所在的库。如果使用常规的方法，将B表拉过来，然后还需要创建 id,cc两个字段的联合索引，这样将消耗大量的空间，并且索引大小会比表还大。所以直接创建id为主键的IOT表，一举两得，唯一的问题可能是在创建这个IOT表的开销会比堆表要大。

--处理超级大量数据时，需要使用extent分块来做，这个是必须的 O(∩_∩)O

在正式开始之前，先对iot和堆表进行一个简单的比较，以便心中有数。

--常规堆表

CREATE TABLE tmp_xf (
id NUMBER,
PARENT_ID NUMBER,
NICK VARCHAR2(32),
GMT_MODIFIED DATE,
PRIMARY KEY (id))
TABLESPACE tbs_crm_dat;

alter SESSION enable parallel dml;

要在dml中开启并行，需要把这个参数打开

INSERT /*+ parallel(a,8) */ INTO tmp_xf a
SELECT /*+ parallel(t,8) */ id,PARENT_ID,NICK,GMT_MODIFIED FROM test_order t;

15mins
简单粗暴的导入方式，耗时15分钟。从并行度可以看到是8个seleclt 8个insert

--索引组织表

CREATE TABLE tmp_xf_iot (
id NUMBER,
PARENT_ID NUMBER,
NICK VARCHAR2(32),
GMT_MODIFIED DATE,
PRIMARY KEY (id))
ORGANIZATION INDEX
TABLESPACE tbs_crm_dat;

14:46:41 SQL> INSERT /*+ parallel(a,8) */ INTO tmp_xf_iot a
14:47:03 2 SELECT /*+ parallel(t,8) */ id,PARENT_ID,NICK,GMT_MODIFIED FROM test_order t;

100501397 ROWS created.

Elapsed: 00:33:41.04

耗时33分钟，是常规表的两倍多，这个时间是可以接受的。毕竟数据导入的同时相当于在维护索引，而且过程中数据在不停的移动。

=======================分割线一点都不华丽==========

读取对比

-----堆表

11:35:32 SQL> SELECT * FROM tmp_xf WHERE id = 2165985;

ID PARENT_ID NICK GMT_MODIFIED
---------- ---------- -------------------------------- -------------------
2165985   0 twork_mengdie13 2010-10-26,16:43:17

Elapsed: 00:00:00.01

Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1973243085

---------------------------------------------------------------------------------------------
| Id | Operation   | Name | ROWS  | Bytes | COST (%CPU)| TIME     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |     |     1 |   53 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TMP_XF |     1 |   53 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN   | SYS_C00131311 |     1 |     |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

STATISTICS
----------------------------------------------------------
  5  consistent gets

可以看到根据主键访问需要5个逻辑读，分析索引的高度是blevel是3，高度high是4，通过索引拿到rowid是4个逻辑读，在回表的话就是5个逻辑读

--索引组织表

11:41:01 SQL> SELECT * FROM tmp_xf_iot WHERE id = 2165985;

ID PARENT_ID NICK GMT_MODIFIED
---------- ---------- -------------------------------- -------------------
2165985   0 twork_mengdie13 2010-10-26,16:43:17

Elapsed: 00:00:00.00

Execution PLAN
----------------------------------------------------------
PLAN hash VALUE: 1560068715

----------------------------------------------------------------------------------------
| Id | Operation   | Name   | ROWS  | Bytes | COST (%CPU)| TIME     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |   |     1 |   53 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_249218 |     1 |   53 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

STATISTICS
----------------------------------------------------------
  4  consistent gets