求一完整的物化视图过程及语句 建物化视图的时候许多步骤不明白,有哪位高手能给以完整过程!!! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 给你找个外援http://www.sqlclub.cn/Develop/2009-03/576_2.htm 以下步骤供参考:----1、建立表日志 TGL_ACCOUNTGROUPS_SUB SELECT COUNT(TABLE_NAME) INTO V_MVLOGCOUNT FROM USER_TABLES WHERE TABLE_NAME='TGL_VOUCHER_MAINS' || V_TABLECODE ; IF V_MVLOGCOUNT > 0 THEN V_SQL :='DROP TABLE ' ; EXECUTE IMMEDIATE V_SQL; END IF; V_SQL :='CREATE MATERIALIZED VIEW log on TGL_ACCOUNTGROUPS_SUB with rowid'; EXECUTE IMMEDIATE V_SQL;2、建立物化视图 MV_TGL_ACCOUNTGROUPS SELECT COUNT(*) INTO V_MVCOUNT FROM USER_MVIEWS WHERE MVIEW_NAME='MV_TGL_ACCOUNTGROUPS'; IF V_MVCOUNT > 0 THEN V_SQL :='DROP MATERIALIZED VIEW MV_TGL_ACCOUNTGROUPS'; EXECUTE IMMEDIATE V_SQL; END IF; V_SQL :='CREATE MATERIALIZED VIEW MV_TGL_ACCOUNTGROUPS' ||' REFRESH complete' ||' AS' ||' SELECT A.UQACCOUNTID,' ||' A.VARCODE VARACCOUNTCODE,' ||' A.VARNAME VARACCOUNTNAME,' ||' SUB.INTLEVEL + 1 INTACCOUNTLEVEL,' ||' SUB.UQACCOUNTSETID,' ||' SUB.VARFORMATCODE,' ||' A.ROWID AID,' ||' SUB.ROWID SUBID' ||' FROM TGL_ACCOUNTS A,' ||' TGL_ACCOUNTGROUPS_SUB SUB' ||' WHERE A.UQACCOUNTGROUPID = SUB.UQACCOUNTGROUPID'; EXECUTE IMMEDIATE V_SQL; 求 高手给看看这个是什么问题 oracle 删除 expdp 导出是提示 文件操作无效 ,高手请指导。 oracle plsql写表分区问题,急!!! 求助触发器错误 oracle9i sqlplus登录问题 用powerdesigner画sqlserver的数据模型图?在线等 高分求助存储过程问题 视图中需要递增号码 哪位能帮我写出这个SQL?? 请教一个ORACLE分析函数查连续6个月以上数据为0的方法! 报表统计的问题,望高手们不吝指教
----1、建立表日志 TGL_ACCOUNTGROUPS_SUB
SELECT COUNT(TABLE_NAME) INTO V_MVLOGCOUNT FROM USER_TABLES WHERE TABLE_NAME='TGL_VOUCHER_MAINS' || V_TABLECODE ;
IF V_MVLOGCOUNT > 0 THEN
V_SQL :='DROP TABLE ' ;
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL :='CREATE MATERIALIZED VIEW log on TGL_ACCOUNTGROUPS_SUB with rowid';
EXECUTE IMMEDIATE V_SQL;
2、建立物化视图 MV_TGL_ACCOUNTGROUPS
SELECT COUNT(*) INTO V_MVCOUNT FROM USER_MVIEWS WHERE MVIEW_NAME='MV_TGL_ACCOUNTGROUPS';
IF V_MVCOUNT > 0 THEN
V_SQL :='DROP MATERIALIZED VIEW MV_TGL_ACCOUNTGROUPS';
EXECUTE IMMEDIATE V_SQL;
END IF;
V_SQL :='CREATE MATERIALIZED VIEW MV_TGL_ACCOUNTGROUPS'
||' REFRESH complete'
||' AS'
||' SELECT A.UQACCOUNTID,'
||' A.VARCODE VARACCOUNTCODE,'
||' A.VARNAME VARACCOUNTNAME,'
||' SUB.INTLEVEL + 1 INTACCOUNTLEVEL,'
||' SUB.UQACCOUNTSETID,'
||' SUB.VARFORMATCODE,'
||' A.ROWID AID,'
||' SUB.ROWID SUBID'
||' FROM TGL_ACCOUNTS A,'
||' TGL_ACCOUNTGROUPS_SUB SUB'
||' WHERE A.UQACCOUNTGROUPID = SUB.UQACCOUNTGROUPID';
EXECUTE IMMEDIATE V_SQL;