ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引组织表和堆组织表的学习

索引组织表和堆组织表的学习

原创 Linux操作系统 作者:david3389 时间:2009-06-29 12:23:40 0 删除 编辑

以下测试用例全部来自于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

scott@PRIMARY>

对堆组织表的查询产生了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

sys@PRIMARY>

合并后空间按比例减少

继续测试其他参数
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

scott@PRIMARY>

执行计划相同,一致性读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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-17

  • 博文量
    19
  • 访问量
    120716