以下测试用例全部来自于TOM.
"纸上得来终觉浅,终知此事要躬行"我不喜欢写大片的文章来描述观点,其实很多理解都是靠做实验做出来的,ORACLE入门难,但是一旦入了门,理解起来就容易多了,完全是水到渠成的事.
一.IOT和堆表(以下简称HEAPT)的DML花费比较
scott@PRIMARY> set timing on;
scott@PRIMARY> insert into heap_addresses
2 select empno, 'LOCATION', '123 main street', 'Washington', 'DC', 20123
3 from empt;
已创建49187行。
已用时间: 00:00:06.97
scott@PRIMARY> insert into iot_addresses
2 select empno, 'LOCATION', '123 main street', 'Washington', 'DC', 20123
3 from empt;
已创建49187行。
已用时间: 00:00:11.74
scott@PRIMARY> update heap_addresses set ADDR_TYPE='NEXT' where addr_type='PREV'
;
已更新49187行。
已用时间: 00: 00: 08.54
scott@PRIMARY> update iot_addresses set ADDR_TYPE='NEXT' where addr_type='PREV';
已更新49187行。
已用时间: 00: 00: 29.52
scott@PRIMARY> delete from heap_addresses;
已删除245935行。
已用时间: 00: 00: 28.29
scott@PRIMARY> delete from iot_addresses;
已删除245935行。
已用时间: 00: 00: 18.55
scott@PRIMARY>
测试结果来看,IOT表在INSERT/UPDATE的时候要慢很多,实际上,这是由于IOT在组织存放数据,数据必须按照主键顺序存放.
从这一点特点来看,IOT并不适合大量的INSERT/UPDATE操作的表,执行速度实在很慢.
好多人说IOT容易产生碎片,这在我看来是不怎么容易出现的.大量数据修改对于MSSM的堆表来说是极易产生碎片的,但是对于ASSM,这种情况就好的多.对于IOT来说,产生碎片的几率同样的低,因为对IOT进行DML操作时候会很慢,为什么慢?因为IOT忙着移动行,调整存放顺序,如果发现当前块无法存储主键数据,它就会移动后面其他数据而保证数据的顺序存储性.当然了,如果存储不下的数据不是主键列,那就直接溢出走好了,IOT本来适应的环境就是主键查询频繁,尽量少的数据修改操作的表,因为IOT的DML操作效率很低.IOT同样也有HWM的问题存在.
二.IOT和HT的查询情况比较.
scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> select * from empt,heap_addresses
2 where emp.empno=heap_addresses.empno
3 and empt.empno=42;
where emp.empno=heap_addresses.empno
*
第 2 行出现错误:
ORA-00904: "EMP"."EMPNO": invalid identifier
scott@PRIMARY> l
1 select * from empt,heap_addresses
2 where emp.empno=heap_addresses.empno
3* and empt.empno=42
scott@PRIMARY> l2
2* where emp.empno=heap_addresses.empno
scott@PRIMARY> c /emp./empt.
2* where empt.empno=heap_addresses.empno
scott@PRIMARY> l
1 select * from empt,heap_addresses
2 where empt.empno=heap_addresses.empno
3* and empt.empno=42
scott@PRIMARY> /
执行计划
----------------------------------------------------------
Plan hash value: 773996588
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 440 | 10(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 440 | 10(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPT | 1 | 42 | 2(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1(0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 5 | 230 | 8(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C005352 | 5 | | 2(0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPT"."EMPNO"=42)
5 - access("HEAP_ADDRESSES"."EMPNO"=42)
统计信息
----------------------------------------------------------
160 recursive calls
0 db block gets
67 consistent gets
5 physical reads
0 redo size
951 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
5 rows processed
scott@PRIMARY> l
1 select * from empt,heap_addresses
2 where empt.empno=heap_addresses.empno
3* and empt.empno=42
scott@PRIMARY> c /heap_/iot_
SP2-0023: 未找到字符串
scott@PRIMARY> l1
1* select * from empt,heap_addresses
scott@PRIMARY> c /heap_/iot_
1* select * from empt,iot_addresses
scott@PRIMARY> l2
2* where empt.empno=heap_addresses.empno
scott@PRIMARY> c /heap_/iot_
2* where empt.empno=iot_addresses.empno
scott@PRIMARY> l
1 select * from empt,iot_addresses
2 where empt.empno=iot_addresses.empno
3* and empt.empno=42
scott@PRIMARY> /
执行计划
----------------------------------------------------------
Plan hash value: 466541008
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 435 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 5 | 435 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPT | 1 | 42 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_51281 | 5 | 225 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMPT"."EMPNO"=42)
4 - access("IOT_ADDRESSES"."EMPNO"=42)
统计信息
----------------------------------------------------------
205 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
951 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
5 rows processed
对堆组织表的查询产生了67个一致性读,而索引组织表只有48个,少了19个.
IOT比HEAP少了TABLE ACCESS BY INDEX ROWID的19个I/O.
模拟两次高频率查询,结果:
SELECT EMPT.ENAME,A.STREET,A.CITY,A.STATE,A.ZIP
FROM
EMPT,HEAP_ADDRESSES A WHERE EMPT.EMPNO=A.EMPNO AND EMPT.EMPNO=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 49187 5.52 4.54 0 0 0 0
Fetch 49187 40.59 39.74 1920 542100 0 245935
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 98375 46.12 44.29 1920 542100 0 245935
SELECT EMPT.ENAME, A.STREET, A.CITY, A.STATE, A.ZIP
FROM
EMPT, IOT_ADDRESSES A WHERE EMPT.EMPNO = A.EMPNO AND EMPT.EMPNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 49187 5.43 4.37 0 0 0 0
Fetch 49187 21.05 18.50 0 299481 0 245935
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 98375 26.48 22.88 0 299481 0 245935
索引就是数据,数据就是索引,越热的表,IOT的查询表现就越出色.
综上所述,单就查询这一环节,IOT完胜,通过主键来访问表所产生的I/O消耗是IOT最突出的特点.
三.空间比较
相同的数据量,相同的索引.
先来看量表的数据量比较,IOT_ADDRESSES和HEAP_ADDRESSES
scott@PRIMARY> select segment_name,ds.segment_type, to_char((bytes / 1024 / 1024
),'99999.99') as "Bytes(M)"
2 from dba_segments ds
3 where wner = 'SCOTT'
4 order by to_char((bytes / 1024 / 1024),'99999.99') desc
5 /
SEGMENT_NAME SEGMENT_TY Bytes(M)
------------------------- ---------- ------------------
BIG_TABLE TABLE 120.00
BIG_TABLE_PK INDEX 17.00
SYS_IOT_TOP_51388 INDEX 14.00
SYS_IOT_TOP_51281 INDEX 14.00
HEAP_ADDRESSES_BAK TABLE 13.81
HEAP_ADDRESSES TABLE 13.00
SYS_C005354 INDEX 8.00
SYS_C005352 INDEX 6.00
EMPT TABLE 3.00
EMP_PK INDEX .88
SYS_IOT_OVER_51390 TABLE .06
SALGRADE TABLE .06
BONUS TABLE .06
PK_EMP INDEX .06
EMP TABLE .06
SYS_IOT_TOP_51390 INDEX .06
PK_DEPT INDEX .06
DEPT TABLE .06
已选择18行。
scott@PRIMARY>
这里有个很奇妙的现象,看不到IOT_ADDRESSES,这就是IOT另外一个突出的特点,"索引就是数据"
这里还可以看出IOT实际的空间消耗要绝对低于HT.在上面的例子可以看出SYS_IOT_TOP_51388(也就是IOT_ADDRESSES)的容量为14M,HEAP_ADDRESSES的空间为19M(HEAP_ADDRESSES+SYS_C005352),这个特点十分重要,不同的情景和参数策略可以造成空间的差异,有可能IOT的空间消耗要远超过于HT.详细测试看第四点.
注:做空间测试时要对所测试的表进行分析,可使用dbms_stats.gather_table_stats('SCOTT','IOT_ADDRESSES',cascade=>true)
scott@PRIMARY> select index_name,table_name from user_indexes
2 /
INDEX_NAME TABLE_NAME
------------------------- -------------------------
SYS_IOT_TOP_51390 IOT2
SYS_IOT_TOP_51388 IOT_ADDRESSES
SYS_IOT_TOP_51281 IOT_ADDRESSES_BAK
SYS_C005354 HEAP_ADDRESSES_BAK
BIG_TABLE_PK BIG_TABLE
SYS_C005352 HEAP_ADDRESSES
EMP_PK EMPT
PK_EMP EMP
PK_DEPT DEPT
已选择9行。
scott@PRIMARY>
这里还有个结论,IOT分配的是索引段.
四.IOT的一些参数特点
NOCOMPRESS/OVERFLOW/INCLUDING
首先测试IOT的NOCOMPRESS参数
使用以下语句:
analyze index SCOTT.SYS_IOT_TOP_51281 validate structure;
select lf_blks,br_blks,used_space,
opt_cmpr_count,opt_cmpr_pctsave
from index_stats;
注意index_stats表,这是个临时表,只保存最近一次分析的索引信息
sys@PRIMARY> analyze index SCOTT.SYS_IOT_TOP_51281 validate structure;
索引已分析
sys@PRIMARY> select lf_blks,br_blks,used_space,
2 opt_cmpr_count,opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
4360 10 13088836 1 4
sys@PRIMARY> alter table scott.iot_addresses move compress 1;
(注意,对于堆表,move以后需要重建索引,因为索引这时候会被置为UNUSABLE,move的测试参见:http://space.itpub.net/16628454/viewspace-607762)
表已更改。
sys@PRIMARY> analyze index SCOTT.SYS_IOT_TOP_51281 validate structure;
索引已分析
sys@PRIMARY> select lf_blks,br_blks,used_space,
2 opt_cmpr_count,opt_cmpr_pctsave
3 from index_stats;
LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
---------- ---------- ---------- -------------- ----------------
1732 4 12421771 1 0
合并后空间按比例减少
继续测试其他参数
PCTTHRESHOLD/OVERFLOW/INCLUDING
scott@PRIMARY> create table iot2
2 (x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index OVERFLOW
7 tablespace test;
表已创建。
scott@PRIMARY> select dbms_metadata.get_ddl('TABLE','IOT2') from dual;
DBMS_METADATA.GET_DDL('TABLE','IOT2')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."IOT2"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PCTTHRESHOLD 50 OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TEST"
scott@PRIMARY> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ -------------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
EMPT TABLE
HEAP_ADDRESSES TABLE
BIG_TABLE TABLE
HEAP_ADDRESSES_BAK TABLE
IOT_ADDRESSES_BAK TABLE
IOT_ADDRESSES TABLE
SYS_IOT_OVER_51390 TABLE
IOT2 TABLE
已选择12行。
scott@PRIMARY> select * from SYS_IOT_OVER_51390 ;
select * from SYS_IOT_OVER_51390
*
第 1 行出现错误:
ORA-25191: cannot reference overflow table of an index-organized table
scott@PRIMARY>
真是太有意思了,当使用参数OVERFLOW创建IOT时,系统会自动创建一个表用于溢出,并且不能对溢出表进行查询.
而且我发现一个相当重要的问题,一些堆表参数IOT是没有的,甚至看不出空BLOCKS,那也就看不到HWM了,有个IOT_TYPE字段可以看出IOT的类型(主要是看IOT主表和溢出表).IOT不能指定BUFFER_POOL的管理方式(DEFAULT,KEEP,RECYCLEBIN),然而,溢出表的管理方式和堆表类似,有PCTFREE和PCTUSED,数据的存放方式应该也和堆表一样.在IOT表上通过ROWID定位记录,IOT主键是不能保存在溢出表上的,必须按顺序保存在制定位置上.
如何使用溢出段可以用参数PCTTHRESHOLD和INCLUDING来指定.
PCTTHRESHOLD是指溢出比例,比如设置这个比例是10,假如数据块是8K,那么这个快一次写入800B的数据,超过的列,溢出.那么假如8K的数据块你打算保存20行,1/20=5%,那么一行的最大容量就是8K*5%=400B.此值默认50,若主键记录非常多且是复合主键并且列很多,那么很容易造成行迁移情况,也可能会造成空间上的浪费.
INCLUDING就是更精确控制溢出的列了,假如你得表有10列,其中前5列最热,最容易被查询,而后5列在绝大多数情况下不会被用到,那么在创建表的时候就可以使用INCLUDING COL5来指定让另5列另行存储,但是后5列绝对不能包含主键列(这里在创建表的规划期就应该将主键列放在最前).
对于以上者两个参数如何使用,是要根据不同的应用模式来考虑的,并且要在不同的情境下做够测试.
五.IOT的二次索引
最后,对于IOT要说的就是建立索引了,这里要引入两个概念,物理猜,逻辑ROWID.故名思意,物理猜是一种猜测,逻辑ROWID不是实际ROWID.
其实原理很简单,当你需要创建额外的索引时,会发现IOT的记录可能是随时移动变更的,那么根据索引的原理,索引的记录要指向一个物理ROWID用来定位记录存储位置,但是IOT上的二次索引就很难做到这一点,无法定位,所以,ORACLE引入了逻辑ROWID的概念,这些逻辑ROWID根据IOT的逐渐建立,对于行的当前存储位置还可以包含一个物理猜,不过这个动作却很难命中,多数情况下,IOT中的数据很快就会被移动到其他位置.因此,IOT上的二次索引效率是比较低的,相对于堆表的索引,IOT上的二次索引会比前者多一些扫描I/O.但是,二次索引有一个特点,可以利用二次索引快速定位主键记录.
用下面的放在在两个表后面各加一列,由代码负责插入随机数,新列值重复的可能性比较小.有兴趣的还可以打开timing看一下运行时间,其中heap_addresses_bak和iot_addresses_bak是事先备份好的.
create table heap_addresses
(empno references empt(empno) on delete cascade,
addr_type varchar2(10),
street varchar2(20),
city varchar2(20),
state varchar2(2),
zip number,
testno number,
primary key (empno,addr_type)
)
tablespace test
/
insert into heap_addresses select a.*,trunc(dbms_random.value(1,50000)) from heap_addresses_bak a;
create table iot_addresses
(empno references empt(empno) on delete cascade,
addr_type varchar2(10),
street varchar2(20),
city varchar2(20),
state varchar2(2),
zip number,
testno number,
primary key (empno,addr_type)
)
organization index
tablespace test
/
insert into iot_addresses select a.*,trunc(dbms_random.value(1,50000)) from iot_addresses_bak a;
create index idx_heap on heap_addresses(testno);
create index idx_iot on iot_addresses(testno);
analyze table heap_addresses compute statistics for table for all indexes for all indexed columns;
analyze table iot_addresses compute statistics for table for all indexes for all indexed columns;
这时候表结构已经完成,先做两次全数据量的查询,比较一下执行计划,注意是走testno上的索引
scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> select * from heap_addresses where testno=45642;
已选择7行。
scott@PRIMARY> select * from heap_addresses where testno=45642;
已选择8行。
执行计划
----------------------------------------------------------
Plan hash value: 1633537100
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 6 | 324 | 7 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 6 | 324 | 7 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_HEAP | 6 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TESTNO"=45642)
统计信息
----------------------------------------------------------
84 recursive calls
0 db block gets
19 consistent gets
4 physical reads
0 redo size
824 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed
scott@PRIMARY> select * from iot_addresses where testno=45642;
执行计划
----------------------------------------------------------
Plan hash value: 1825120558
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 6 | 324 | 21 (0)| 00:
00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_51425 | 6 | 324 | 21 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN| IDX_IOT | 6 | | 3 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTNO"=45642)
2 - access("TESTNO"=45642)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
727 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
执行计划相同,一致性读IOT略微占优,在没有数据变化的情况下,IOT的表现还是略好,二次索引物理猜一次命中数据.下一步,改变数据分布,删除ADDR_TYPE='LOCATION'的数据,再插入ADDR_TYPE='XXX'的数据
scott@PRIMARY> delete from heap_addresses where ADDR_TYPE='LOCATION';
已删除49187行。
scott@PRIMARY> delete from IOT_addresses where ADDR_TYPE='LOCATION';
已删除49187行。
scott@PRIMARY> insert into heap_addresses
2 select empno, 'XXX', '123 main street', 'Washington', 'DC', 20123
3 from empt;
insert into heap_addresses
*
第 1 行出现错误:
ORA-00947: not enough values
scott@PRIMARY> edit
已写入 file afiedt.buf
1 insert into heap_addresses
2 select empno, 'XXX', '123 main street', 'Washington', 'DC', 20123,trunc(dbm
s_random.value(1,50000))
3* from empt
scott@PRIMARY> /
已创建49187行。
scott@PRIMARY> insert into iot_addresses
2 select empno, 'XXX', '123 main street', 'Washington', 'DC', 20123,trunc(dbm
s_random.value(1,50000))
3 from empt
4 /
已创建49187行。
scott@PRIMARY> commit;
提交完成。
scott@PRIMARY> analyze table heap_addresses compute statistics for table for all
indexes for all indexed columns;
表已分析。
scott@PRIMARY> analyze table iot_addresses compute statistics for table for all
indexes for all indexed columns;
表已分析。
scott@PRIMARY>
这个时候我想IOT和HT的记录位置应该和原先的不一样了,再来看看执行计划(这里我重启了一下数据库,目的是清空shared_pool和buffer_cache)
scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> select * from heap_addresses where testno=8412;
执行计划
----------------------------------------------------------
Plan hash value: 1633537100
--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 6 | 318 | 9 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 6 | 318 | 9 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_HEAP | 6 | | 3 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TESTNO"=8412)
统计信息
----------------------------------------------------------
557 recursive calls
0 db block gets
118 consistent gets
19 physical reads
0 redo size
766 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
5 rows processed
scott@PRIMARY> select * from iot_addresses where testno=8412;
已选择7行。
执行计划
----------------------------------------------------------
Plan hash value: 1825120558
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 7 | 371 | 17 (0)| 00:
00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_51425 | 7 | 371 | 17 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN| IDX_IOT | 7 | | 3 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TESTNO"=8412)
2 - access("TESTNO"=8412)
统计信息
----------------------------------------------------------
534 recursive calls
0 db block gets
129 consistent gets
19 physical reads
0 redo size
793 bytes sent via SQL*Net to client
392 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
7 rows processed
scott@PRIMARY>
这个时候IOT比HT的查询多了11个I/O,这就是因为行记录位置改变,物理猜不能命中,继而去取逻辑ROWID(由主键组成的ROWID)定位记录,所以,会比HT多一些扫描逻辑ROWID的I/O
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16628454/viewspace-607795/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16628454/viewspace-607795/
633
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
