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.