Oracle:物化视图创建及刷新的脚本.
2010-04-15 15:50 Tracy. 阅读(2257) 评论(0) 编辑 收藏 举报SET DEFINE OFF;
DROP MATERIALIZED VIEW TMS.TMS_LOCATION;
CREATE MATERIALIZED VIEW TMS.TMS_LOCATION
TABLESPACE TMSDATA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS
select tl.*,decode(tl.location_type,'VENDOR',null,wpbu.business_unit_4) branch4,decode(tl.location_type,'VENDOR',null,wpbu.business_unit) branch5, decode(tvx.new_vendor_no,null,null,wl.location_id) vendor_location,tvx.new_vendor_no,tvx.old_vendor
from wms.wms_intf_location_tms_vw tl, wms.wms_location wl, tms_ven_xref tvx ,wms_ps_business_unit wpbu
where
tl.location_id=wpbu.campus_id(+) and
tl.location_id=wl.location_id and
SUBSTR ('000000' || tvx.new_vendor_no(+),
-10
)
|| SUBSTR ('0000000000' || tvx.new_location(+),
-10
) = wl.location_name;
COMMENT ON MATERIALIZED VIEW TMS.TMS_LOCATION IS 'snapshot table for snapshot
TMS.TMS_LOCATION';
CREATE UNIQUE INDEX TMS.TMS_LOCATION_PK ON TMS.TMS_LOCATION
(LOCATION_ID)
LOGGING
TABLESPACE TMSDATA
NOPARALLEL;
GRANT SELECT ON TMS.TMS_LOCATION TO TMS_GUEST;
dbms_mview.REFRESH ('TMS_LOCATION');
本文来自博客园,作者:Tracy.,转载请注明原文链接:https://www.cnblogs.com/tracy/archive/2010/04/15/1712743.html