Oracle不走索引hint,为什么用了hint还是不走index

看了支付宝面试:select min(id),max(id) 优化问题的帖子，做实验发现奇怪现象，如下：

SQL>  select * from v$version where rownum<2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

SQL>  create table test as select * from dba_objects;

表已创建。

SQL> create index i_object_id on test(object_id);

索引已创建。

SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TEST',cascade => true);

PL/SQL 过程已成功完成。

SQL> set autot trace exp stat

SQL> select max(object_id),min(object_id) from test;

执行计划

----------------------------------------------------------

Plan hash value: 1950795681

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     5 |   293   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |

|   2 |  TABLE ACCESS FULL| TEST | 73350 |   358K|   293   (1)| 00:00:04 |

---------------------------------------------------------------------------

这个可以理解，cbo自己决定的(但是还是有点不合理)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

1049  consistent gets

0  physical reads

0  redo size

502  bytes sent via SQL*Net to client

416  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select /*+ index(testi_object_id) */max(object_id),min(object_id) from test;

执行计划

----------------------------------------------------------

Plan hash value: 1950795681

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     5 |   293   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |

|   2 |   TABLE ACCESS FULL| TEST | 73350 |   358K|   293   (1)| 00:00:04 |

---------------------------------------------------------------------------

这个我已经使用了hint index提示了，为什么还是全表扫描？

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

1049  consistent gets

0  physical reads

0  redo size

502  bytes sent via SQL*Net to client

416  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select max(object_id),min(object_id) from test where object_id>0;

执行计划

----------------------------------------------------------

Plan hash value: 1751978921

--------------------------------------------------------------------------------

-----

| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time

|

--------------------------------------------------------------------------------

-----

|   0 | SELECT STATEMENT      |             |     1 |     5 |    47   (3)| 00:00

:01 |

|   1 |  SORT AGGREGATE       |             |     1 |     5 |            |

|

|*  2 |  INDEX FAST FULL SCAN| I_OBJECT_ID | 73347 |   358K|    47   (3)| 00:00

:01 |

-------------------------------------------------------------------------------------

我添加了一个where条件object_id，就走index了

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_ID">0)

统计信息

----------------------------------------------------------

1  recursive calls

0  db block gets

170  consistent gets

0  physical reads

0  redo size

502  bytes sent via SQL*Net to client

416  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

问题是：不加where条件的情况下，加不加hint，都不会使用INDEX FAST FULL SCAN