数据库版本：9.2.0.1.0
query_rewrite_enabled=true
query_rewrite_integrity=enforced
mvtest 物化视图的创建脚本如下：
create materialized view mvtest
enable query rewrite
as
select distinct gbv.head_id,
gbv.vou_no,
gbv.set_year,
gbv.set_month,
substr(gbv.bill_date, 9, 2) as set_day,
substr(gbv.bill_date,1,10) as bill_date,
1 as bills,
gbv.vou_money as sum_money,
sbt.billtype_code DataSource_Code,
gc.MK_CODE,
decode(substr(gc.mk_code,0,3),001,1,002,2,003,3,004,4,005,5) as set_code,
gc.PK_CODE,
substr(gbv.latest_op_date, 1, 10) as last_date
from gl_bus_voucher_his gbv
joinGL_CCIDS gc on gc.ccid = gbv.ccid
left join ele_book_set ebs on gbv.st_id = ebs.chr_id
left join sys_billtype sbt on gbv.billtype_id = sbt.billtype_id
where ebs.chr_code = 0;
创建完毕后，执行如下查询（即：创建mvtest物化视图的子查询），并查看执行计划
select distinct gbv.head_id,
gbv.vou_no,
gbv.set_year,
gbv.set_month,
substr(gbv.bill_date, 9, 2) as set_day,
substr(gbv.bill_date,1,10) as bill_date,
1 as bills,
gbv.vou_money as sum_money,
sbt.billtype_code DataSource_Code,
gc.MK_CODE,
decode(substr(gc.mk_code,0,3),001,1,002,2,003,3,004,4,005,5) as set_code,
gc.PK_CODE,
substr(gbv.latest_op_date, 1, 10) as last_date
from gl_bus_voucher_his gbv
joinGL_CCIDS gc on gc.ccid = gbv.ccid
left join ele_book_set ebs on gbv.st_id = ebs.chr_id
left join sys_billtype sbt on gbv.billtype_id = sbt.billtype_id
where ebs.chr_code = 0;
经过检查执行计划，发现还是访问基表，没有发生query rewrite，没有访问mvtest物化视图。
说明：物化视图创建完后，没有一个基表发生更新。请问是不是给“from gl_bus_voucher_his gbv
joinGL_CCIDS gc on gc.ccid = gbv.ccid
left join ele_book_set ebs on gbv.st_id = ebs.chr_id
left join sys_billtype sbt on gbv.billtype_id = sbt.billtype_id
where ebs.chr_code = 0;
”这种写法有关，如果不是，有谁知道具体原因是什么吗？ |
