索引回表操作，ORACLE所作的优化

最新推荐文章于 2021-04-03 01:53:10 发布

转载最新推荐文章于 2021-04-03 01:53:10 发布 · 414 阅读

文章标签：

create table t2 as select * from dba_objects where 1=0;
begin
for i in 1 ... 1000000 loop
insert into t2(object_id) values(SEQ_AP_ISSUE.nextval);
commit;
end loop;
end;
/
select
  2  /*+ gather_plan_statistics index(t2) */
  3  dbms_rowid.ROWID_RELATIVE_FNO(rowid) rfile#,
  4 dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#
  5  from
  6  t2
  7  where
  8  object_id between 120000 and 120110
  9  ;
RFILE#    BLOCK#
---------- ----------
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
      13    51211
      13    51208
共计111条记录
select
  2  /*+ gather_plan_statistics index(t2) */
  3  count(object_name)
  4  from
  5  t2
  6  where
  7  object_id between 120000 and 120110
  8  ;
COUNT(OBJECT_NAME)
------------------
               0

select * from table(dbms_xplan.display_cursor('f2gq4d8xdm382',null,'iostats last'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f2gq4d8xdm382, child number 0
-------------------------------------
select /*+ gather_plan_statistics index(t2) */ count(object_name) from
t2 where object_id between 120000 and 120110
Plan hash value: 2103910247
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |    |    1 |       |    1 |00:00:00.01 |    112 |
| 1 |  SORT AGGREGATE             |    |    1 |    1 |    1 |00:00:00.01 |    112 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 |    1 | 111 | 111 |00:00:00.01 |    112 |
|*  3 | INDEX RANGE SCAN       | TTTT |    1 | 111 | 111 |00:00:00.01 |    4 |
-----------------------------------------------------------------------------------------------
112-4=98，ORACLE只是对连续能命中一个块的索引键值回表操作做了优化，只回表一次，而对于1 2 1 2 1 2这种索引键值，只是每次读取一个键值就回表一次

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-764372/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-764372/