Hello All,
How do i make or push the Oracle optimizer to choose the range scan instead of skip scan?
Plan hash value: 1371174339
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 8386 | 384K| 800 (0)| 00:00:10 |
| 1 | DELETE | LOAN | | | | |
|* 2 | INDEX SKIP SCAN| IDX_LOAN_02 | 8386 | 384K| 800 (0)| 00:00:10 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("L"."VER_NUM"=3 AND "L"."EXTRACT_DT"='2012-12-31' AND
"L"."UNINUM"='925000')
filter("L"."VER_NUM"=3 AND "L"."EXTRACT_DT"='2012-12-31' AND
"L"."UNINUM"='925000' AND TO_NUMBER("L"."PROVIDER")=6)
SQL Statement:
EXPLAIN PLAN FOR
DELETE /*+ INDEX(L IDX_LOAN_02) */ FROM loan L WHERE L.provider=6 AND L.ver_num=3 AND L.EXTRACT_DT='2012-12-31' AND L.UNINUM='925000'
Table Index:
| Column_Name | Index_Name | Index_Type | Uniqueness | Tablespace_Name | Column_Position |
| SURROG_LOAN_NUM | IDX_LOAN_01 | NORMAL | NONUNIQUE | FCSDWH_STGIND | 1 |
| PROVIDER | IDX_LOAN_02 | NORMAL | NONUNIQUE | FCSDWH_STGIND | 1 |
| VER_NUM | IDX_LOAN_02 | NORMAL | NONUNIQUE | FCSDWH_STGIND | 2 |
| EXTRACT_DT | IDX_LOAN_02 | NORMAL | NONUNIQUE | FCSDWH_STGIND | 3 |
| UNINUM | IDX_LOAN_02 | NORMAL | NONUNIQUE | FCSDWH_STGIND | 4 |
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
Thank you