省市县物化视图

CREATE MATERIALIZED VIEW mv_base_province_list
TABLESPACE tbl_pro --保存表空间 
BUILD IMMEDIATE --创建视图时就生成数据 
REFRESH FORCE --如果可以快速刷新则进行快速刷新，否则完全刷新 
on demand --按照指定方式刷新 
START WITH SYSDATE --第一次刷新时间 
next SYSDATE + 1 as
select case
         when t3.p_name is not null then
          t3.p_name || ',' || t2.p_name || ',' || t1.p_name
         when t2.p_name is not null then
          t2.p_name || ',' || t1.p_name
         else
          t1.p_name
       end as p_full_name,
       t1.*
  from base_province_list t1
  left join base_province_list t2 on t1.par_index = t2.p_index
                                 and t2.par_index is not null
  left join base_province_list t3 on t2.par_index = t3.p_index
                                 and t3.par_index is not null

--物化视图索引
create unique index pk_mv_base_province_list on mv_base_province_list(P_INDEX) tablespace tbl_pro_IDX;
create index idx_mv_bpl_dm on mv_base_province_list (DEL_MARK) tablespace tbl_pro_IDX;
create index idx_mv_bpl_pai on mv_base_province_list (PAR_INDEX) tablespace tbl_pro_IDX;

--强制刷新 c：全局刷新 f：增量刷新 空：默认刷新
call DBMS_MVIEW.REFRESH('mv_base_province_list','C');