Selectivity-索引的可选择性

同事反应这个查询“较慢”，并且没有走索引：
select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9102';

经分析，这是一个400万的表：
select count(*) from pl_georoute;
4473003

以上查询结果集达到50万：
select count(*) from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9102';
508381

于是想到了，根据oracle对于SQL执行计划基于成本的优化原则，这么大的结果集，相对于400万的表来讲，索引不如全表扫描快。
之所以反应慢，估计是结果太大，加上网络传输等其他环节，因此在应用看来“较慢”。

以下做验证。

对于相对较大的结果集，尽管建立了相应的索引，但是oracle可以“任性的无视”。
SQLPLUS> explain plan for select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9102';
Explained.
SQLPLUS> select * from table(dbms_xplan.display);
---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |   472K|   195M| 55636   (1)| 00:11:08 |
|*  1 |  TABLE ACCESS FULL| PL_GEOROUTE |   472K|   195M| 55636   (1)| 00:11:08 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ROUTE_TYPE"=U'9102' AND "STATEFLAG"=0)

而对于可选择性【selectivity】较好的查询，oracle会乖乖的重视这个索引。
SQLPLUS> explain plan for select * from pl_georoute where STATEFLAG = 0 and ROUTE_TYPE = '9101';
Explained.
SQLPLUS> select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   788 |   334K|    96   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PL_GEOROUTE  |   788 |   334K|    96   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | R175_UNION_1 |   903 |       |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ROUTE_TYPE"=U'9101' AND "STATEFLAG"=0)