Comparison of execution plan changes used by index_ss hint

Comparison of execution plan changes used by index_ss hint

Buffer indicates the number of memory reads in the current operation, including consistent read and current read.

Although the number of records in the EMP table is small, the number of buffer Read Memories remains a little different

 

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
Zookeeper --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _id at8ssqpn41css, Child number 0
-------------------------------------
Select/* + index_ss (EMP I _emp) */job from EMP where ename = 'Smith'

Plan hash value: 3956160932

Bytes ------------------------------------------------------------------------------------
| ID | operation | Name | starts | E-rows | A-time | buffers |
Bytes ------------------------------------------------------------------------------------
| * 1 | table access full | EMP | 1 | 1 | 1 | 00:00:00. 01 | 8 |
Bytes ------------------------------------------------------------------------------------

Plan_table_output
Zookeeper --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

1-filter ("ename" = 'Smith ')

17 rows selected.

 

---- Create an index

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
Zookeeper --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL _id ck2pc7bpbzdz8, Child number 0
-------------------------------------
Select/* + index_ss (EMP I _emp) */job from EMP where ename = 'Smith'

Plan hash value: 98078853

Bytes -----------------------------------------------------------------------------------------------
| ID | operation | Name | starts | E-rows | A-time | buffers |
Bytes -----------------------------------------------------------------------------------------------
| 1 | table access by index rowid | EMP | 1 | 1 | 00:00:00. 01 | 3 |
| * 2 | index skip scan | I _emp | 1 | 1 | 1 | 00:00:00. 01 | 2 |

Plan_table_output
Zookeeper --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bytes -----------------------------------------------------------------------------------------------

Predicate information (identified by Operation ID ):
---------------------------------------------------

2-access ("ename" = 'Smith ')
Filter ("ename" = 'Smith ')

19 rows selected.

Comparison of execution plan changes used by index_ss hint