hi I am using version 11.2.0.4.0 of oracle. There is one query which is going for index scan and full table scan based on one constant value. when i use -999999 i.e. six 9's the query is going for full table scan, but in other combinations like five 9's and seven 9's its going for index range scan. also when i hint the index its cost going up.
There exist index on column ZE, when i use six 9's as filter criteria, its doesn't use index in a simple query though. Where as for other combinations(five 9's or seven 9's) its using index, i have published here the histogram details for that column. would you able to help me, what might be the cause behind this? or its really a optimizer bug?
When i tried with -1*999999, it started using index. So i am bit confused here, my concern is , as i see very wired behaviour of optimizer so is it safe to compare here any other combination of 9's except six? or any other way we should handle?
I want to analyze this so that it wont affect some other queries in our database.
column_name, data_tyPe, num_distinct,density, num_nulls, histogram
ZE NUMBER 48970450 7.46653127356624E-6 58718 HEIGHT BALANCED
select column_name, endpoint_number, endpoint_value from dba_tab_histograms where table_name='ZZE' and column_name='ZE'
SQL> select I.ZZIID from ZZI i where i.ZE=-99999;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1088234794
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZI | 1 | 14 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZZI_IX2 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."ZE"=(-99999))
SQL> select I.ZZIID from ZZI i where i.ZE=-9999999;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1088234794
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZI | 1 | 14 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZZI_IX2 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."ZE"=(-9999999))
SQL> select I.ZZIID from ZZI i where i.ZE=-999999;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2104890136
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 54481 (6)| 00:04:37 |
|* 1 | TABLE ACCESS STORAGE FULL| ZZI | 1 | 20 | 54481 (6)| 00:04:37 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("I"."ZE"=(-999999))
SQL> select I.ZZIID from ZZI i where i.ZE=-1*999999;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1088234794
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZI | 1 | 14 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZZI_IX2 | 1 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("I"."ZE"=(-999999))
1 - filter("I"."ZE"=(-999999))
------------- By forcing it to go for index with Six 9's------------------------
SQL> select /*+INDEX(I ZZI_IX2)*/ I.ZZIID from ZZI i where i.ZE=-999999;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3306559304
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 185K (1)| 00:15:40 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZZI | 1 | 20 | 185K (1)| 00:15:40 |
|* 2 | INDEX FULL SCAN | ZZI_IX2 | 1 | | 185K (1)| 00:15:40 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("I"."ZE"=(-999999))