2.1.6，外部数据源是oracle，测试物化视图失效

Question

doris版本：2.1.6
以下流程，用mysql的数据源，都能改写到物化视图上，但oracle出现以下问题：
DROP MATERIALIZED VIEW mv1;
CREATE MATERIALIZED VIEW mv1
BUILD IMMEDIATE REFRESH AUTO ON SCHEDULE EVERY 1 DAY STARTS "2024-11-21 19:10:00"
DISTRIBUTED BY RANDOM BUCKETS 2
PROPERTIES ('replication_num' = '1')
AS
select
a.ID --oracle数据类型NUMBER(18,0)
,a.DATE_ID --oracle数据类型NUMBER(18,0)
,b.cal_date --oracle数据类型varchar2
,c.office_name --oracle数据类型varchar2
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;

--会话设置：
SET enable_nereids_planner = true;
SET enable_materialized_view_rewrite = true;
SET materialized_view_rewrite_enable_contain_external_table = true;
SET materialized_view_rewrite_success_candidate_num = 3;
SET enable_materialized_view_union_rewrite = true;
SET enable_materialized_view_nest_rewrite = true;
SET materialized_view_relation_mapping_max_count = 8;

----------(走了物化视图）
explain
select
a.ID
,a.DATE_ID
,b.cal_date
,c.office_name
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;

----------(不走了物化视图）和第一个sql的差异就是去掉了：c.office_name
explain
select
a.ID
,a.DATE_ID
,b.cal_date
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;

----------(不走了物化视图）
explain
select
c.office_name,count(1) as total
from oracle_test.TEST.test01 a
left join oracle_test.TEST.test02 b on a.date_id=b.id
left join oracle_test.TEST.test03 c on a.BILLING_OFFICE_ID=c.id;
group by c.office_name;

seawinde · Answer

物化视图的透明改写目前分为两步

根据sql结构信息判断是否能改写
如果改写成功，将改写后的plan注册到代价模型中，基于代价选择最终的执行plan

这个问题看着是改写成功了，但是最后代价模型没有选,explain中关键词为 ```
MaterializedViewRewriteSuccessButNotChose

oracle 和 mysql的表数据是否一致？

还有可以试试运行如下语句，看看能否让 cbo 选中
analyze table oracle_test.TEST.test01 with sync;
analyze table oracle_test.TEST.test02 with sync;
analyze table oracle_test.TEST.test03 with sync;

2.1.6，外部数据源是oracle，测试物化视图失效

1 Answers