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博客

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

最新文章