oracle 建立物化视图

1.百度oracle快速刷新物化视图限制

简单总结几点

  1 查询表必须有rowid 

  2 查询所有表必须建立物化视图日志()

  物化视图必须有日志才能做到日志快速刷新

  3 列明不要重复

  sql例子

  

select twater0_.hid as HID, twater0_.METER_ID as METER_ID, twater0_.IN_FLOW as IN_FLOW,
twater0_.OUT_FLOW as OUT_FLOW, twater0_.FLOW_SPEED as FLOW_SPEED, 
twater0_.IN_TEMPERATURE as IN_TEMPERATURE, twater0_.ALL_WORK_TIME as ALL_WORK_TIME, 
twater0_.ALL_TIME as ALL_TIME, twater0_.SYS_READ_TIME as SYS_READ_TIME, 
tstatetype4_.STATE_DESC as STATE_DESC, twater0_.NOTES as NOTES, 
twater0_.MEMO1 as MEMO1, twater0_.MEMO2 as MEMO2, twater0_.MEMO3 as MEMO3, twater0_.MEMO4 as MEMO4,
twater0_.MEMO5 as MEMO5,twater0_.MEMO6 as MEMO6, twater0_.CREATE_DATE as CREATE_DATE,
twater0_.CREATE_USER as CREATE_USER,twater0_.UPDATE_DATE as UPDATE_DATE, 
twater0_.UPDATE_USER as UPDATE_USER, 
tmeter1_.BUILDID as BUILDID, 
tbuild2_.BUILDNAME as BUILDNAME, 
troom3_.DOORNO as DOORNO, troom3_.UNITNO as UNITNO
from energy.water_meter_data twater0_, energy.meter_info tmeter1_, LGD.LGD_BUILD_INFO tbuild2_, 
LGD.LGD_ROOM_INFO troom3_, energy.meter_state_define tstatetype4_ 
where twater0_.METER_ID=tmeter1_.hid 
and tmeter1_.BUILDID=tbuild2_.HID 
and tmeter1_.CONSUMER_ID=troom3_.HID 
and tmeter1_.METER_TYPE='5' 
and twater0_.RUNNING_STATE=tstatetype4_.RUNNING_STATE 
and 2=2 
order by twater0_.hid, twater0_.METER_ID desc 

客户端操作图解

1.授权

2 建立物化视图及 日志文件