Right, following some fiddling around and internal discussions, I believe the histogram and the mod predicate are red herrings. I can reproduce similar behaviour with the following test case. Note there's no histograms or wacky predicates:
create table t (
c1 not null , c2 not null, c3, c4
) as
select level, mod ( level, 17 ) c2,
lpad ( 'x', 100, 'x' ) c3,
mod(level,2) c4
from dual
connect by level <= 500000;
create index i on t ( c1, c2 );
select count(c3) from t
where c2 = 0;
exec dbms_stats.gather_table_stats ( user, 't',method_opt=>'for all columns size 1' ) ;
select column_name, num_distinct, num_buckets, histogram
from user_tab_col_statistics
where table_name = 'T'
order by column_name;
COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
C1 500000 1 NONE
C2 17 1 NONE
C3 1 1 NONE
C4 2 1 NONE
alter session set statistics_level = all;
set serveroutput off
select count(c3) from t
where c2 = 0;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +COST'));
PLAN_TABLE_OUTPUT
SQL_ID a9yy44qsyfqjf, child number 1
-------------------------------------
select count(c3) from t where c2 = 0
Plan hash value: 1507512123
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1796 (100)| 1 |00:00:00.15 | 9386 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.15 | 9386 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 29412 | 1796 (1)| 29411 |00:00:00.14 | 9386 |
|* 3 | INDEX SKIP SCAN | I | 1 | 29412 | 1320 (0)| 29411 |00:00:00.11 | 1321 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("C2"=0)
filter("C2"=0)
select /*+ full ( t ) */count(c3) from t
where c2 = 0;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST +COST'));
PLAN_TABLE_OUTPUT
SQL_ID 6c8j5fzj5tgjd, child number 0
-------------------------------------
select /*+ full ( t ) */count(c3) from t where c2 = 0
Plan hash value: 2966233522
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2229 (100)| 1 |00:00:00.04 | 8074 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.04 | 8074 |
|* 2 | TABLE ACCESS FULL| T | 1 | 29412 | 2229 (1)| 29411 |00:00:00.04 | 8074 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C2"=0) So it looks like in this case the optimizer assigning too low a cost to the skip scan. If you want us to address this, please raise this with support.
In the meantime as other predicates give a full scan, I suggest you find another way to carve up your load. The mod rowid trick looks a little shonky to me anyway!