Why full table scan even with lower index scan cost?

See following test case.

1. Version

UKJA@ukja102> select * from v$version;

BANNER
-------------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2. Create objects

create table t1(c1 int, c2 int, c3 char(100));

insert into t1
select level, level, 'x'
from dual
connect by level <= 1000
;

create index t1_n1 on t1(c1, c2);  <-- Note that c1 is leading column

exec dbms_stats.gather_table_stats(user, 't1');

3. Now we execute following query. Because leading column(c1) is inexistent in predicates, FTS is expected.

select *
from t1
where c2 = 1  <-- Note that leading column is not given!
;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

4. But there is a problem. With index hint given, Oracle expects lower cost with index full scan. The cost is 5 compared to 7 of FTS.

select /*+ index(t1) */ *
from t1
where c2 = 1
;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

5. Now we have a very serious problem.

Why doesn’t Oracle choose index full scan which has much lower?

I’m very familiar with this phenomenon where Oracle denies to use index when leading column is inexistent in predicates. Sometimes we hit index skip scan instead of FTS, but why Oracle denies to use index full scan?

This behavior is consistent in all versions except in 9.2.0.1 where index full scan cost is much higher.

-- 9.2.0 (without index hint)
-------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |   108 |     7  (15)|
|*  1 |  TABLE ACCESS FULL   | T1          |     1 |   108 |     7  (15)|
-------------------------------------------------------------------------

-- 9.2.0 (with index hint)
--------------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |   108 |    28   (4)|
|*  1 |  TABLE ACCESS FULL          | T1          |     1 |   108 |     7  (15)|
|*  2 |   INDEX FULL SCAN           | T1_N1       |     1 |       |    27   (4)|
--------------------------------------------------------------------------------

-- 9.2.0 (10053 trace)
SINGLE TABLE ACCESS PATH
Column:         C2  Col#: 2      Table: T1   Alias: T1
    NDV: 1000      NULLS: 0         DENS: 1.0000e-003 LO:  1  HI: 1000
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: T1     ORIG CDN: 1000  ROUNDED CDN: 1  CMPTD CDN: 1
  Access path: tsc  Resc:  6  Resp:  6
  Skip scan: ss-sel 0  andv 1000 
    ss cost 1000
    table io scan cost 6
  Access path: index (no sta/stp keys)
      Index: T1_N1
  TABLE: T1
      RSC_CPU: 193379   RSC_IO: 27
  IX_SEL:  1.0000e+000  TB_SEL:  1.0000e-003
  BEST_CST: 7.00  PATH: 2  Degree:  1

-- 10.1.0.2 (without index hint)
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 10.1.0.2 (with index hint)
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 10.1.0.2 (10053 trace)
SINGLE TABLE ACCESS PATH
  COLUMN:         C2(NUMBER)  Col#: 2      Table: T1   Alias: T1
    Size: 4  NDV: 1000  Nulls: 0  Density: 1.0000e-003 Min: 1  Max: 1000
    No Histogram: #BKT: 1
        (1 uncompressed buckets and 2 endpoint values)
  TABLE: T1  Alias: T1    
    Original Card: 1000  Rounded Card: 1  Computed Card: 1.00
  Access Path: table-scan  Resc:  6  Resp:  6
  Access Path: index (skip-scan)
    ss sel 1.0000e-003  andv 1000 
    ss cost 1000 vs. table scan io cost 6
    Skip Scan rejected
  Access Path: index (no start/stop keys)
    Index: T1_N1
    rsc_cpu: 35997   rsc_io: 5
    ix_sel:  1.0000e+000    ix_sel_with_filters:  1.0000e-003
  BEST_CST: 6.03  PATH: 2  Degree:  1

-- 10.2.0.1 (without index hint)
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 10.2.0.1 (with index hint)
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 10.2.0.1 (10053 trace)
SINGLE TABLE ACCESS PATH
  Column (#2): C2(NUMBER)
    AvgLen: 4.00 NDV: 1000 Nulls: 0 Density: 1.0000e-003 Min: 1 Max: 1000
  Table: T1  Alias: T1    
    Card: Original: 1000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  7.04  Resp: 7.04  Degree: 0
      Cost_io: 7.00  Cost_cpu: 362449
      Resp_io: 7.00  Resp_cpu: 362449
kkofmx: index filter:"T1"."C2"=1
  Access Path: index (skip-scan)
    SS sel: 1.0000e-003  ANDV (#skips): 1000
    SS io: 1000.00 vs. table scan io: 7.00
    Skip Scan rejected
  Access Path: index (FullScan)
    Index: T1_N1
    resc_io: 5.00  resc_cpu: 235797
    ix_sel: 1  ix_sel_with_filters: 1.0000e-003
    Cost: 5.03  Resp: 5.03  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 7.04  Degree: 1  Resp: 7.04  Card: 1.00  Bytes: 0

-- 11.1.0.6 (without index hint)
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   108 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   108 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

-- 11.1.0.6 (with index hint)
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   108 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |   108 |     5   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | T1_N1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

-- 11.1.0.6 (10053 trace)
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T1[T1]
  Table: T1  Alias: T1
    Card: Original: 1000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  7.04  Resp: 7.04  Degree: 0
      Cost_io: 7.00  Cost_cpu: 362449
      Resp_io: 7.00  Resp_cpu: 362449
kkofmx: index filter:"T1"."C2"=1

  Access Path: index (skip-scan)
    SS sel: 0.001000  ANDV (#skips): 1000.000000
    SS io: 1000.000000 vs. table scan io: 7.000000
    Skip Scan rejected

  Access Path: index (FullScan)
    Index: T1_N1
    resc_io: 5.00  resc_cpu: 235797
    ix_sel: 1.000000  ix_sel_with_filters: 0.001000
 ***** Logdef predicate Adjustment ******
 Final IO cst 0.00 , CPU cst 50.00
 ***** End Logdef Adjustment ******
    Cost: 5.03  Resp: 5.03  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 7.04  Degree: 1  Resp: 7.04  Card: 1.00  Bytes: 0

The funny thing is that Oracle did consider the index ful scan and found it to have lower cost! But it silently denied to use it.

Oracle would have chosen index skip scan with lower cost!

Is this a bug or designed feature?