Analysis on "hidden parameter optimizer_skip_scan_enabled"

optimizer_skip_scan_enabled = false.

I have done a little bit observation on this parameter and find some
think different with and without this parameter.

Steps:

1) Create a table.
2) Create composite index on table.
3) Runned query with two different values of
optimizer_skip_scan_enabled. As true and false.

Observation.

1) If we take optimizer_skip_scan_enabled as false.
   Then query is taking Index_range_scan as a result of which it is
consuming huge number of rows for giving the output.
2) And if we take optimizer_skip_scan_enabled as true.
   Then query is taking Index_skip_scan and output is coming by
traversing only 1 row.

Analysis:

Hidden parameter _optimizer_skip_scan_enabled Explanation.

==========================================================

alter session set "_optimizer_skip_scan_enabled"=false;

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  9hk2mp74npcn8, child number 0

-------------------------------------

select /* abc */ S,U,T,E,W,TODAY_DATE 

from test where EMP_ID='22608' and EXE_NAME='XYZ' and H='AB000Z'

Plan hash value: 3441529208

------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                  |       |       |     1 (100)|          |

|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST             |     1 |    46 |     1   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_H       |   152 |       |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(("EMP_ID"='22608' AND "EXE_NAME"='XYZ'))

   2 - access("H"='AB000Z')

22 rows selected.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

alter session set "_optimizer_skip_scan_enabled"=true;

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID  awvqwbdn4zy12, child number 0

-------------------------------------

select /* def */ S,U,T,E,W,TODAY_DATE 

from test where EMP_ID='22608' and EXE_NAME='XYZ' and H='AB000Z'

Plan hash value: 2342613384

-------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                   |       |       |     1 (100)|          |

|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST              |     1 |    46 |     1   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | IDX_TEST_L |     1 |       |     1   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("HOME_SOL_ID"='AB000Z')

   2 - access("EMP_ID"='22608' AND "EXE_NAME"='XYZ')

       filter(("EMP_ID"='22608' AND "EXE_NAME"='XYZ'))

=====================================================================================

index_details

==================

SQL> select index_name,column_name,column_position from dba_ind_columns where table_name='TEST';

INDEX_NAME                     COLUMN_NAM COLUMN_POSITION

------------------------------                         ---------- ---------------

IDX_TEST_L               R_ID                  1

IDX_TEST_L               EMP_ID                2

IDX_TEST_L               EXE_NAME              3

IDX_TEST_LO           S_ID                  1

IDX_TEST_LO           EMP_ID                2

IDX_TEST_HS               H_ID                  1

                               

table details

==================

SQL> desc test

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 S                                         VARCHAR2(15)

 U                                         VARCHAR2(15)

 T                                         VARCHAR2(2)

 E                                         VARCHAR2(10)

 W                                         VARCHAR2(8)

 EXE_NAME                                  VARCHAR2(25)