1.补充一点资料:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
SQL> create table t1 as select rownum id ,'test' name from dual connect by levelSQL> alter table t1 add constraint pk_t1 PRIMARY KEY(id) USING INDEX(CREATE INDEX i_t1_id ON t1(id,name)) ENABLE ;
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T1', estimate_percent=> null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
SQL> select index_name from user_indexes where table_name='T1';
INDEX_NAME
------------------------------
I_T1_ID
2.
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 51
consistent gets from cache 51
consistent gets - examination 5
consistent gets direct 0
SQL> select id,name from t1 where id=60 ;
ID NAME
---------- ----
60 test
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 53(+2)
consistent gets from cache 53(+2)
consistent gets - examination 5(+0)
consistent gets direct 0
--失望!oracle 看来还是不够聪明,执行计划依旧是索引范围扫描。看来要想实现一个逻辑读，只能使用IOT表。
SQL> select * from table(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9r3x00whhy46y, child number 0
-------------------------------------
select id,name from t1 where id=60
Plan hash value: 4406210
---------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------
|* 1 | INDEX RANGE SCAN| I_T1_ID | 1 |
---------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=60)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
23 rows selected.
3.在11Gr2下重复测试：
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 37
consistent gets from cache 37
consistent gets from cache (fastpath) 33
consistent gets - examinatio 4
consistent gets direct 0
SQL> select id,name from t1 where id=60 ;
ID NAME
---------- ----
60 test
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 39(+2)
consistent gets from cache 39(+2)
consistent gets from cache (fastpath) 34(+1)
consistent gets - examination 4(+0)
consistent gets direct 0
--与10g一样，不过11G增加一个统计信息consistent gets from cache (fastpath).并且有1个读,以后研究看看。
补充: 如果执行这样呢？select id,name from t1 where id=60 and rownum
SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 37
consistent gets from cache 37
consistent gets from cache (fastpath) 33
consistent gets - examination 4
consistent gets direct 0
15:09:28 SQL> select id,name from t1 where id=60 and rownum
ID NAME
---------- ----
60 test
15:09:54 SQL> select b.name,a.value from v$mystat a, v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'consistent gets%';
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 38
consistent gets from cache 38
consistent gets from cache (fastpath) 33
consistent gets - examination 4
consistent gets direct 0
逻辑读仅仅为1，不过一般情况下不会这样写。
