我创建物化视图mv_xxtj ,索引建在ghrq字段上,可在适用查询统计的时候仅仅是用到了物化视图,但并没用上物化视图上的索引,为什么?怎么可以使他用上呢?
以下是执行计划:
1SELECTsum(cq) as cq,hzfz as unit
2FROM gdh_xxzb a,gdh_mon b WHERE 1 = 1 and a.id = b.id and sign(cq) = 1
3 and GhRq>=TO_DATE('2004-06-30 11:01:30', 'YYYY-MM-DD HH24:MI:SS')
4 and GhRq /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=210 Bytes=35
70)
10 SORT (ORDER BY) (Cost=19 Card=210 Bytes=3570)
21 SORT (GROUP BY) (Cost=19 Card=210 Bytes=3570)
32 TABLE ACCESS (FULL) OF 'MV_XXTJ' (Cost=12 Card=613 Byt
es=10421)
14:50:16 SQL> edit
Wrote file afiedt.buf
1SELECT/*+ index(mv_xxtj ind_mv_xxtj_mon) */sum(cq) as cq,hzfz as unit
2FROM gdh_xxzb a,gdh_mon b WHERE 1 = 1 and a.id = b.id and sign(cq) = 1
3 and GhRq>=TO_DATE('2004-06-30 11:01:30', 'YYYY-MM-DD HH24:MI:SS')
4 and GhRq /
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=210 Bytes=35
70)
10 SORT (ORDER BY) (Cost=19 Card=210 Bytes=3570)
21 SORT (GROUP BY) (Cost=19 Card=210 Bytes=3570)
32 TABLE ACCESS (FULL) OF 'MV_XXTJ' (Cost=12 Card=613 Byt
es=10421) |