帮忙写个物化视图

Question

需求：数据库有一张视图查询比较忙，想把它改成物化视图，不知道能不能提高查询速度？？？要求物化视图时时查询结果和原视图时时查询结果是一致的，不能有时间差。普通视图脚本：create or replace view vw_gl_voudetail 
as
select gv.ds_id,
       gv.define_no,
       gv.bills,
       gv.v_type,
       gv.is_covered,
       gv.cov_head_id,
       gv.maker_id,
       gv.manager_id,
       gv.bill_create_user,
       gv.auditor_id,
       gv.keeper_id,
       gv.rg_code,
       gv.maker_name,
       gv.manager_name,
       gv.auditor_name,
       gv.keeper_name,
       gv.voucher_id,
       gvd.voucher_detail_id,
       gv.set_year,
       gv.period_id,
       gv.period_name,
       gv.voucher_no,
       gv.status,
       gvd.main_line,
       gvd.detail_line,
       gv.vou_date,
       gvd.summary,
       gvd.re,
       gvd.as_id,
       (select eas.chr_code
          from ele_accountant_subject eas
         where eas.chr_id = gvd.as_id
           and eas.st_id = gv.st_id) as as_code,
       (select eas.chr_name
          from ele_accountant_subject eas
         where eas.chr_id = gvd.as_id
           and eas.st_id = gv.st_id) as as_name,
       gvd.ccid,
       gv.ST_ID,
       gvd.deb_money,
       gvd.cre_money,
       gvd.BAC_ID,
       gvd.BAC_CODE,
       gvd.BAC_NAME,
       gvd.BAI_ID,
       gvd.BAI_CODE,
       gvd.BAI_NAME,
       gvd.BAP_ID,
       gvd.BAP_CODE,
       gvd.BAP_NAME,
       gvd.BI_ID,
       gvd.BI_CODE,
       gvd.BI_NAME,
       gvd.BIS_ID,
       gvd.BIS_CODE,
       gvd.BIS_NAME,
       gvd.BK_ID,
       gvd.BK_CODE,
       gvd.BK_NAME,
       gvd.BL_ID,
       gvd.BL_CODE,
       gvd.BL_NAME,
       gvd.BO_ID,
       gvd.BO_CODE,
       gvd.BO_NAME,
       gvd.BP_ID,
       gvd.BP_CODE,
       gvd.BP_NAME,
       gvd.BS_ID,
       gvd.BS_CODE,
       gvd.BS_NAME,
       gvd.BSI_ID,
       gvd.BSI_CODE,
       gvd.BSI_NAME,
       gvd.BT_ID,
       gvd.BT_CODE,
       gvd.BT_NAME,
       gvd.CB_ID,
       gvd.CB_CODE,
       gvd.CB_NAME,
       gvd.CT_ID,
       gvd.CT_CODE,
       gvd.CT_NAME,
       gvd.EDITOR_ID,
       gvd.EDITOR_CODE,
       gvd.EDITOR_NAME,
       gvd.EN_ID,
       gvd.EN_CODE,
       gvd.EN_NAME,
       gvd.FILE_ID,
       gvd.FILE_CODE,
       gvd.FILE_NAME,
       gvd.FM_ID,
       gvd.FM_CODE,
       gvd.FM_NAME,
       gvd.IB_ID,
       gvd.IB_CODE,
       gvd.IB_NAME,
       gvd.INPM_ID,
       gvd.INPM_CODE,
       gvd.INPM_NAME,
       gvd.IN_BIS_ID,
       gvd.IN_BIS_CODE,
       gvd.IN_BIS_NAME,
       gvd.IN_BS_ID,
       gvd.IN_BS_CODE,
       gvd.IN_BS_NAME,
       gvd.MB_ID,
       gvd.MB_CODE,
       gvd.MB_NAME,
       gvd.MK_ID,
       gvd.MK_CODE,
       gvd.MK_NAME,
       gvd.OPUSER_ID,
       gvd.OPUSER_CODE,
       gvd.OPUSER_NAME,
       gvd.PB_ID,
       gvd.PB_CODE,
       gvd.PB_NAME,
       gvd.PK_ID,
       gvd.PK_CODE,
       gvd.PK_NAME,
       gvd.PM_ID,
       gvd.PM_CODE,
       gvd.PM_NAME,
       gvd.SM_ID,
       gvd.SM_CODE,
       gvd.SM_NAME,
       gvd.GB_ID,
       gvd.GB_CODE,
       gvd.GB_NAME,
       gvd.IEN_ID,
       gvd.IEN_CODE,
       gvd.IEN_NAME
  from gl_voucher gv, gl_voucher_detail gvd
 where gv.voucher_id = gvd.voucher_id;
备注：
1、gl_voucher表的voucher_id是索引，gl_voucher_detail表的voucher_id也是索引。
2、gl_voucher_detail表和gl_voucher表数据量比较大。

Answer 1

--1.以SYS用户，为用户授权（如用户HIS），以便拥有创建视图权限
grant create materialized view to HIS;
grant query rewrite to HIS;
grant on commit refresh to HIS;--以HIS库用户，如用户HIS，身份进入HIS数据库，并执行２,３操作。请严格按顺序执行
--2.创建视图LOG
--已做过物化视图日志的表格不需要重复建立
CREATE MATERIALIZED VIEW LOG ON gl_voucher WITH PRIMARY KEY INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON gl_voucher_detail WITH PRIMARY KEY INCLUDING NEW VALUES;--3.创建物化视图
CREATE materialized VIEW name_VIEW refresh fast
WITH PRIMARY KEY AS
...这里加入LZ写的查询SQL...数据量应该不成问题，我试过2千万左右的两张表联接查询，查询时间都是在0.1秒以下
但使用refresh fast可能会因为更新频率过高，导致业务表锁死。
如果没有特别严格的要求的话，可以考虑适当延时。
如把refresh fast改成：
                     REFRESH FAST 
                  START WITH SYSDATE 
                  NEXT  SYSDATE + 2--每天更新一次