index_ss hint 使用的执行计划变化对比 其中 buffer 代表：当前操作中发生的内存读次数，包含一致性读和当前读 虽然 emp 表记录数不多，但是buffer 读内存的次数差别还是有点大的 sql select job from emp where ename=smith; job ------------------ clerk
index_ss hint 使用的执行计划变化对比
其中 buffer 代表：当前操作中发生的内存读次数，包含一致性读和当前读
虽然 emp 表记录数不多，但是buffer 读内存的次数差别还是有点大的
sql> select job from emp where ename='smith';
job
------------------
clerk
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
plan_table_output
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_id at8ssqpn41css, child number 0
-------------------------------------
select /*+ index_ss(emp i_emp)*/ job from emp where ename='smith'
plan hash value: 3956160932
------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
------------------------------------------------------------------------------------
|* 1 | table access full| emp | 1 | 1 | 1 |00:00:00.01 | 8 |
------------------------------------------------------------------------------------
plan_table_output
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(ename='smith')
17 rows selected.
----创建一个索引
sql> create index i_emp on emp(empno, ename);
index created.
sql> select /*+ index_ss(emp i_emp)*/ job from emp where ename='smith';
job
------------------
clerk
sql> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
2 ;
plan_table_output
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sql_id ck2pc7bpbzdz8, child number 0
-------------------------------------
select /*+ index_ss(emp i_emp)*/ job from emp where ename='smith'
plan hash value: 98078853
-----------------------------------------------------------------------------------------------
| id | operation | name | starts | e-rows | a-rows | a-time | buffers |
-----------------------------------------------------------------------------------------------
| 1 | table access by index rowid| emp | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | index skip scan | i_emp | 1 | 1 | 1 |00:00:00.01 | 2 |
plan_table_output
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - access(ename='smith')
filter(ename='smith')
19 rows selected.