oracle materialized view 不刷新,materialized view的刷新机制与问题

参加培训，知道了物化视图这个新鲜玩意儿，立马想把该功能引入系统：

hp11i＋oraocle9204

1.建立物化视图，参照yangtingkun的几篇专述，解决了不少问题

create materialized view log on tlw_sett_f3_200503 with rowid,sequence(year_id,month_id,day_id,switch_id,innet_region_code,outnet_region_code,access_code,sett_type,direction_id,calling_tsp_code,called_tsp_code,num_cdr,duration,settlement_duration,sett_count,local_discount_fee,toll_discount_fee,sett_fee) including new values;

create materialized view js_mv_tlw_sett_f3_200503

tablespace zhjs_list_wjjs_200503

parallel 4

build immediate

REFRESH fast START WITH trunc(sysdate,'dd') NEXT trunc(sysdate,'dd')+1 + 4/24

enable query rewrite

as

select /*+parallel(tlw_sett_f3_200503,16)*/

t.year_id,

t.month_id,

t.day_id,

t.switch_id,

t.innet_region_code,

t.outnet_region_code,

t.access_code,

t.sett_type,

t.direction_id,

t.calling_tsp_code,

t.called_tsp_code,

count(t.num_cdr) count_cdrs,

count(t.duration) count_durs,

count(t.settlement_duration) count_sdurs,

count(t.sett_count) count_scounts,

count(t.local_discount_fee) count_ldisfees,

count(t.toll_discount_fee) count_tdisfees,

count(t.sett_fee) count_sfees,

count(*) count_star,

sum(t.num_cdr) cdrs,

sum(t.duration) durs,

sum(t.settlement_duration) sdurs,

sum(t.sett_count) scounts,

sum(t.local_discount_fee) ldisfees,

sum(t.toll_discount_fee) tdisfees,

sum(t.sett_fee) sfees

from tlw_sett_f3_200503 t

group by

t.year_id,

t.month_id,

t.day_id,

t.switch_id,

t.innet_region_code,

t.outnet_region_code,

t.access_code,

t.sett_type,

t.direction_id,

t.calling_tsp_code,

t.called_tsp_code;

2.单独测试

建成后当天都是可以自动查询重写的

为了使用查询重写，我还特地让大家使用rewrite暗示

3.但是事隔2天，有人说问题出现了，查看对应的执行计划

select /*+rewrite*/t.year_id,t.month_id,t.day_id ,b.area_code,sum(t.num_cdr)

from tlw_sett_f3_200503 t,s_area b,tp_source c

where t.switch_id = b.area_code||c.area_source_id

group by t.year_id,t.month_id,t.day_id,b.area_code

SELECT STATEMENT, GOAL = CHOOSE                        Cost=590255495        Cardinality=11        Bytes=440

SORT GROUP BY                        Cost=590255495        Cardinality=11        Bytes=440

HASH JOIN                        Cost=29337        Cardinality=156235049537        Bytes=6249401981480

MERGE JOIN CARTESIAN                        Cost=820        Cardinality=167281        Bytes=4349306

TABLE ACCESS FULL        Object owner=JSZHJS        Object name=S_AREA        Cost=2        Cardinality=409        Bytes=5317

BUFFER SORT                        Cost=818        Cardinality=409        Bytes=5317

TABLE ACCESS FULL        Object owner=JSZHJS        Object name=TP_SOURCE        Cost=2        Cardinality=409        Bytes=5317

TABLE ACCESS FULL        Object owner=JSZHJS        Object name=TLW_SETT_F3_200503        Cost=18828        Cardinality=24283160        Bytes=339964240

select /*+rewrite*/sum(:cdrs) from tlw_sett_f3_200503

SELECT STATEMENT, GOAL = CHOOSE                        Cost=18828        Cardinality=1

SORT AGGREGATE                                Cardinality=1

TABLE ACCESS FULL        Object owner=JSZHJS        Object name=TLW_SETT_F3_200503        Cost=18828        Cardinality=24283160

4.奇怪了，怎么就不行呢？

5.望经验的大侠们不吝赐教。