省市县物化视图
CREATE MATERIALIZED VIEW mv_base_province_listTABLESPACE tbl_pro --保存表空间 BUILD IMMEDIATE --创建视图时就生成数据 REFRESH FORCE --如果可以快速刷新则进行快速刷新，否则完全刷新 on demand --按照指定方式刷新 START WITH SYSDATE --第一次刷新时间 next SYSDATE + 1 asselect 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');