为了更快的定位您的问题，请提供以下信息，谢谢
【详述】基于5个表创建了一个异步物化视图，各表信息如下，查询语句中的join表是物化视图的子集，想要使用view delta join改写，不会自动改写，查询的还是基表
查询1命中了物化视图
explain SELECT u.cust_id, u.order_time, u.order_no, a.age_name, a.agentid_lv1, pm.age_name AS pm_age_name, pm.chan_type, sc.age_cust_id AS sc_age_cust_id, sc.bus_addr, sc.bus_name,sc.phone, sc.fall_aget_id AS sc_fall_aget_id, u.ustldat– ,scode.code_name, scode.source
FROM testdb.cs_tcode_his_sr AS u
LEFT OUTER JOIN testdb.cs_pk_code_sr AS scode ON scode.code_no = u.code_no
LEFT OUTER JOIN testdb.cs_pk_cus_sr AS sc ON sc.cust_id = u.cust_id
LEFT OUTER JOIN testdb.cs_pk_agt_full_sr AS a ON a.cust_id = u.age_cust_id
LEFT OUTER JOIN testdb.cs_pk_agt_sr AS pm ON pm.cust_id = u.age_cust_id
where u.order_time>=‘20240401’ and u.order_time<‘20240405’ and sc.phone=‘12345678901’ ;
查询2未命中物化视图:
explain SELECT u.cust_id, u.order_time, u.order_no, a.age_name, a.agentid_lv1, pm.age_name AS pm_age_name, pm.chan_type, sc.age_cust_id AS sc_age_cust_id, sc.bus_addr, sc.bus_name,sc.phone, sc.fall_aget_id AS sc_fall_aget_id, u.ustldat– ,scode.code_name, scode.source
FROM testdb.cs_tcode_his_sr AS u
– LEFT OUTER JOIN testdb.cs_pk_code_sr AS scode ON scode.code_no = u.code_no
LEFT OUTER JOIN testdb.cs_pk_cus_sr AS sc ON sc.cust_id = u.cust_id
LEFT OUTER JOIN testdb.cs_pk_agt_full_sr AS a ON a.cust_id = u.age_cust_id
LEFT OUTER JOIN testdb.cs_pk_agt_sr AS pm ON pm.cust_id = u.age_cust_id
where u.order_time>=‘20240401’ and u.order_time<‘20240405’ and sc.phone=‘12345678901’ ;
查询2执行计划如下:
未命中物化视图语句执行计划.txt (3.7 KB)
【背景】
【业务影响】查询未命中物化视图
【是否存算分离】否
【StarRocks版本】2.5.19
【集群规模】3fe（1 follower+2observer）+12be（fe与be混部）
【机器信息】40C/128G/万兆
【联系方式】 StarRocks社区群5 -思变 [1412195108@qq.com]
附件:
创建表和物化视图相关sql.txt (33.5 KB)