oracle9的索引和count的问题，急啊！！

[lveyo]

一个普通的数据表，有十几个varchar2的字段，在主键和个别字段上建有索引
运行sql语句：

SQL> select * from (select rownum as rid, t1.* from (select id,hphm,a.lkbh as lk
mc,to_char(jlrq,'yyyy-mm-dd hh24:mi:ss') as jlrq,clsd,'详情' as xq, decode(hpzl,
'01','大型汽车','02','小型汽车','其他') as hpzl,decode(jllx,'0','卡口数据','1','
违法数据','卡口数据') as jllx,decode(sfxg,'0','未修改','1','已修改','未修改') as
sfxg, decode(jllx,1,decode (sign((clsd-xzsd)/xzsd-0.5),1,'1603','1303'),'未超速
') as wfdm from kakou.veh_info a where a.jlrq>to_date('2006-10-02','yyyy-mm-dd'
) ) t1 where rownum<=100 ) t2 where t2.rid>0;

已选择100行。

已用时间: 00: 01: 117.53

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=7324 Card=100 Bytes=
9700)

1 0 VIEW (Cost=7324 Card=100 Bytes=9700)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'VEH_INFO' (Cost=7324 Card=1249
51 Bytes=7122207)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
70601 consistent gets
70536 physical reads
0 redo size
11768 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed



SQL> select count(a.id) from veh_info a where a.jlrq>to_date('2006-10-02','yyyy-
mm-dd');

COUNT(A.ID)
-----------
15160

已用时间: 00: 00: 00.25

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=359 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'IN_VEH_INFO_JLRQ' (NON-UNIQUE) (C
ost=359 Card=124951 Bytes=999608)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
107 consistent gets
0 physical reads
0 redo size
373 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


表veh_info有500万数据，已经进行过全表统计分析，用的是dbms_stats.gather_table_stats包括索引。

以上两个sql语句的查询条件都一样，一个是显示内容，一个是统计条数，为什么运行计划相差这么多，一个用到索引，一个没有用到索引。

还有就是，查询语句有两个条件，分别都建有索引，单个条件查询的时候都用到了索引，两个条件组合起来就用不到索引了，这个为什么呢？

[wiler]

不要那么多子查询
改成这样试试
select * from (select rownum as rid,id,hphm,a.lkbh as lk
mc,to_char(jlrq,'yyyy-mm-dd hh24:mi:ss') as jlrq,clsd,'详情' as xq, decode(hpzl,
'01','大型汽车','02','小型汽车','其他') as hpzl,decode(jllx,'0','卡口数据','1','
违法数据','卡口数据') as jllx,decode(sfxg,'0','未修改','1','已修改','未修改') as
sfxg, decode(jllx,1,decode (sign((clsd-xzsd)/xzsd-0.5),1,'1603','1303'),'未超速
') as wfdm from kakou.veh_info a where a.jlrq>to_date('2006-10-02','yyyy-mm-dd')
where rownum<=100 ) t2 where t2.rid>0