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.
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.
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)
