创建异步物化视图视图后发现无法命中

Viewed 18
mtmv

版本信息：Doris v2.1.6

创建物化视图

CREATE MATERIALIZED VIEW mv_wwb_test5
BUILD IMMEDIATE
REFRESH AUTO
ON SCHEDULE EVERY 10 hour
DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
as
select F5.dt AS dt,
F5.ALL_ AS ALL_,
F5.BPY AS BPY,
F5.AER AS AER,
F5._ctm_cd_4b8b854e9827483c91d4130de817b900 AS _ctm_cd_4b8b854e9827483c91d4130de817b900,
count(distinct F5.D_TFC_01231) AS D_TFC_01231,
null AS ctm_m_ba21795a99404092b2a3369f547ff933_tb_y_ratio,
null AS D_ORD_05480_zb_rt_ratio,
null AS D_ORD_05480_tb_y_ratio,
(
count(
distinct case
when F5.BQO in ('轮胎')
and F5.ABI in ('app') then F5.D_TFC_01231
else null
end
)
) AS D_TFC_01271,
null AS D_ORD_05480,
null AS D_ORD_02745,
if(F5.dt is null, pow(2, 4), 0) + if(F5.ALL is null, pow(2, 3), 0) + if(F5.BPY is null, pow(2, 2), 0) + if(F5.AER is null, pow(2, 1), 0) + if(
F5._ctm_cd_4b8b854e9827483c91d4130de817b900 is null,
pow(2, 0),
0
) as grp_v
from
(
select
vf.dim_prd_display_name_level1 as ABD,
vf.dim_tfc_platform_type as ABI,
if(
coalesce(concat('', vf.entrance_position_name), '') in('', 'null'),
'(null)',
concat('', vf.entrance_position_name)
) as AER,
vf.dim_source_level1_name as AFM,
if(
coalesce(concat('', vd1.tire_spec), '') in('', 'null'),
'(null)',
concat('', vd1.tire_spec)
) as ALL,
if(
coalesce(concat('', vf.entrance_resource_position), '') in('', 'null'),
'(null)',
concat('', vf.entrance_resource_position)
) as BPY,
vf.dim_analysis_business as BQO,
if(
coalesce(concat('', vf.tid_car_year_range), '') in('', 'null'),
'(null)',
concat('', vf.tid_car_year_range)
) as BRD,
substring(vf.dt, 1, 10) as _com_d,
if(
coalesce(
concat(
'',
(
CASE
WHEN vf.tid_car_year_range = '10年以上' THEN '7-10+'
WHEN vf.tid_car_year_range = '7-10年' THEN '7-10+'
WHEN vf.tid_car_year_range = '4-6年' THEN '4-6'
WHEN vf.tid_car_year_range = '2-3年' THEN '0-3'
WHEN vf.tid_car_year_range = '0-1年' THEN '0-3'
else '无车龄'
end
)
),
''
) in('', 'null'),
'(null)',
concat(
'',
(
CASE
WHEN vf.tid_car_year_range = '10年以上' THEN '7-10+'
WHEN vf.tid_car_year_range = '7-10年' THEN '7-10+'
WHEN vf.tid_car_year_range = '4-6年' THEN '4-6'
WHEN vf.tid_car_year_range = '2-3年' THEN '0-3'
WHEN vf.tid_car_year_range = '0-1年' THEN '0-3'
else '无车龄'
end
)
)
) as ctm_cd_4b8b854e9827483c91d4130de817b900,
replace(substring(vf.dt, 1, 7), '-', '') as dt,
vf.keypage_prd_listing_deviceid as D_TFC_01231
from
bi_test.ads_tfc_conv_platform_goods_sum_di vf
left join bi_olap.dim_prd_product_f vd1 on vf.prdid = vd1.prdid
WHERE
vf.dim_tfc_platform_type IN ('app')
AND vf.dim_prd_display_name_level1 IN ('轮胎')
AND vf.dim_source_level1_name IN ('线上自有平台', '线下门店')
AND vf.dt between '2025-12-01'
and '2025-12-31'
) F5
group by
grouping sets (
(
F5.dt,
F5.ALL,
F5.BPY,
F5.AER,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(
F5.dt,
F5.ALL,
F5.BPY,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(
F5.dt,
F5.ALL,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(
F5.dt,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(F5.ctm_cd_4b8b854e9827483c91d4130de817b900),(F5.dt, F5.ALL, F5.BPY, F5.AER),(),(F5.dt, F5.ALL, F5.BPY),(F5.dt, F5.ALL),(F5.dt)
)
;

查询物化视图状态

image.png

查询后发现无法命中，查询语句如下

explain select F5.dt AS dt,
F5.ALL_ AS ALL_,
F5.BPY AS BPY,
F5.AER AS AER,
F5._ctm_cd_4b8b854e9827483c91d4130de817b900 AS _ctm_cd_4b8b854e9827483c91d4130de817b900,
count(distinct F5.D_TFC_01231) AS D_TFC_01231,
null AS ctm_m_ba21795a99404092b2a3369f547ff933_tb_y_ratio,
null AS D_ORD_05480_zb_rt_ratio,
null AS D_ORD_05480_tb_y_ratio,
(
count(
distinct case
when F5.BQO in ('轮胎')
and F5.ABI in ('app') then F5.D_TFC_01231
else null
end
)
) AS D_TFC_01271,
null AS D_ORD_05480,
null AS D_ORD_02745,
if(F5.dt is null, pow(2, 4), 0) + if(F5.ALL is null, pow(2, 3), 0) + if(F5.BPY is null, pow(2, 2), 0) + if(F5.AER is null, pow(2, 1), 0) + if(
F5._ctm_cd_4b8b854e9827483c91d4130de817b900 is null,
pow(2, 0),
0
) as grp_v
from
(
select
vf.dim_prd_display_name_level1 as ABD,
vf.dim_tfc_platform_type as ABI,
if(
coalesce(concat('', vf.entrance_position_name), '') in('', 'null'),
'(null)',
concat('', vf.entrance_position_name)
) as AER,
vf.dim_source_level1_name as AFM,
if(
coalesce(concat('', vd1.tire_spec), '') in('', 'null'),
'(null)',
concat('', vd1.tire_spec)
) as ALL,
if(
coalesce(concat('', vf.entrance_resource_position), '') in('', 'null'),
'(null)',
concat('', vf.entrance_resource_position)
) as BPY,
vf.dim_analysis_business as BQO,
if(
coalesce(concat('', vf.tid_car_year_range), '') in('', 'null'),
'(null)',
concat('', vf.tid_car_year_range)
) as BRD,
substring(vf.dt, 1, 10) as _com_d,
if(
coalesce(
concat(
'',
(
CASE
WHEN vf.tid_car_year_range = '10年以上' THEN '7-10+'
WHEN vf.tid_car_year_range = '7-10年' THEN '7-10+'
WHEN vf.tid_car_year_range = '4-6年' THEN '4-6'
WHEN vf.tid_car_year_range = '2-3年' THEN '0-3'
WHEN vf.tid_car_year_range = '0-1年' THEN '0-3'
else '无车龄'
end
)
),
''
) in('', 'null'),
'(null)',
concat(
'',
(
CASE
WHEN vf.tid_car_year_range = '10年以上' THEN '7-10+'
WHEN vf.tid_car_year_range = '7-10年' THEN '7-10+'
WHEN vf.tid_car_year_range = '4-6年' THEN '4-6'
WHEN vf.tid_car_year_range = '2-3年' THEN '0-3'
WHEN vf.tid_car_year_range = '0-1年' THEN '0-3'
else '无车龄'
end
)
)
) as ctm_cd_4b8b854e9827483c91d4130de817b900,
replace(substring(vf.dt, 1, 7), '-', '') as dt,
vf.keypage_prd_listing_deviceid as D_TFC_01231
from
bi_test.ads_tfc_conv_platform_goods_sum_di vf
left join bi_olap.dim_prd_product_f vd1 on vf.prdid = vd1.prdid
WHERE
vf.dim_tfc_platform_type IN ('app')
AND vf.dim_prd_display_name_level1 IN ('轮胎')
AND vf.dim_source_level1_name IN ('线上自有平台', '线下门店')
AND vf.dt between '2025-12-01'
and '2025-12-31'
) F5
group by
grouping sets (
(
F5.dt,
F5.ALL,
F5.BPY,
F5.AER,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(
F5.dt,
F5.ALL,
F5.BPY,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(
F5.dt,
F5.ALL,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(
F5.dt,
F5.ctm_cd_4b8b854e9827483c91d4130de817b900
),(F5.ctm_cd_4b8b854e9827483c91d4130de817b900),(F5.dt, F5.ALL, F5.BPY, F5.AER),(),(F5.dt, F5.ALL, F5.BPY),(F5.dt, F5.ALL),(F5.dt)
)
;
image.png

问题：

创建物化视图和查询的select语句都相同，为啥查询无法命中物化视图

2 Answers

v2.1.6 中，异步物化视图“定义里带 GROUPING SETS”时，确实无法被透明改写命中，这和你遇到的情况一致。
想利用 GROUPING SETS 的查询命中 MV，推荐做法是：
MV 用普通 GROUP BY 预聚合；
查询侧写 GROUPING SETS，让优化器做“多维聚合改写”。
如果你现在的 MV 是 GROUPING SETS 写在视图里，建议改成普通 GROUP BY，把多维聚合逻辑放在查询侧，这样在 2.1.6 才有机会命中。

徐振超@SelectDB8021

创建第一个group by

CREATE MATERIALIZED VIEW mv_wwb_test10
BUILD IMMEDIATE
REFRESH AUTO
ON SCHEDULE EVERY 10 hour
DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
as
select null as BQO,
F1.dt AS dt,
sum(F1.O_ORD_00014) AS O_ORD_00014,
sum(F1.O_ORD_00061) AS O_ORD_00061,
sum(F1.O_ORD_00020) AS O_ORD_00020,
sum(F1.O_ORD_00001) AS O_ORD_00001,
sum(F1.O_ORD_00336) AS O_ORD_00336,
sum(F1.O_ORD_00065) AS O_ORD_00065,
sum(F1.O_ORD_00066) AS O_ORD_00066,
count(distinct F1.O_ORD_00021) AS O_ORD_00021
from
(
select
vf.businessline as ABA,
vf.dim_order_source_channel_category_lvl1_name as AFM,
if(
coalesce(concat('', vf.dim_analysis_business), '') in('', 'null'),
'(null)',
concat('', vf.dim_analysis_business)
) as BQO,
vf.is_2c_order_pay as CBT,
vf.dim_cost_businessline as CGW,
vf.dim_sale_mainbody_type as CMB,
substring(vf.dt, 1, 10) as _com_d,
substring(vf.dt, 1, 10) as dt,
vf.amt_a2 as O_ORD_00001,
vf.pay_gmv as O_ORD_00014,
vf.finish_order_gmv as O_ORD_00020,
vf.finish_order_user as O_ORD_00021,
vf.finish_order_orderid as O_ORD_00022,
vf.amt_b0 as O_ORD_00061,
vf.gross_profit_no_service_a15 as O_ORD_00065,
vf.gross_profit_no_service_a15 as O_ORD_00065_avg_by_d,
vf.gross_profit_no_service_a16 as O_ORD_00066,
vf.gross_profit_no_service_a16 as O_ORD_00066_avg_by_d,
vf.amt_a15 as O_ORD_00336
from
bi_test.ads_ord_order_detail_dtl_di vf
WHERE
vf.businessline IN ('保养', '洗美', '轮胎')
AND vf.dim_order_source_channel_category_lvl1_name IN ('线上自有平台', '线下门店')
AND vf.dt between '2025-12-01'
and '2025-12-31'
) F1
group by F1.dt
;

explain 上面的select ,提示可以命中，如截图
image.png

创建第二个group by

CREATE MATERIALIZED VIEW mv_wwb_test11
BUILD IMMEDIATE
REFRESH AUTO
ON SCHEDULE EVERY 10 hour
DISTRIBUTED BY RANDOM BUCKETS 3
PROPERTIES ('replication_num' = '1')
as
select
F1.BQO AS BQO,
F1.dt AS dt,
sum(F1.O_ORD_00014) AS O_ORD_00014,
sum(F1.O_ORD_00061) AS O_ORD_00061,
sum(F1.O_ORD_00020) AS O_ORD_00020,
sum(F1.O_ORD_00001) AS O_ORD_00001,
sum(F1.O_ORD_00336) AS O_ORD_00336,
sum(F1.O_ORD_00065) AS O_ORD_00065,
sum(F1.O_ORD_00066) AS O_ORD_00066,
count(distinct F1.O_ORD_00021) AS O_ORD_00021
from
(
select
vf.businessline as ABA,
vf.dim_order_source_channel_category_lvl1_name as AFM,
if(
coalesce(concat('', vf.dim_analysis_business), '') in('', 'null'),
'(null)',
concat('', vf.dim_analysis_business)
) as BQO,
vf.is_2c_order_pay as CBT,
vf.dim_cost_businessline as CGW,
vf.dim_sale_mainbody_type as CMB,
substring(vf.dt, 1, 10) as _com_d,
dt ,
vf.amt_a2 as O_ORD_00001,
vf.pay_gmv as O_ORD_00014,
vf.finish_order_gmv as O_ORD_00020,
vf.finish_order_user as O_ORD_00021,
vf.finish_order_orderid as O_ORD_00022,
vf.amt_b0 as O_ORD_00061,
vf.gross_profit_no_service_a15 as O_ORD_00065,
vf.gross_profit_no_service_a15 as O_ORD_00065_avg_by_d,
vf.gross_profit_no_service_a16 as O_ORD_00066,
vf.gross_profit_no_service_a16 as O_ORD_00066_avg_by_d,
vf.amt_a15 as O_ORD_00336
from
bi_test.ads_ord_order_detail_dtl_di vf
WHERE
vf.businessline IN ('保养', '洗美', '轮胎')
AND vf.dim_order_source_channel_category_lvl1_name IN ('线上自有平台', '线下门店')
AND vf.dt between '2025-12-01'
and '2025-12-31'
) F1
group by F1.BQO, F1.dt
;

explain 上面的select ,提示可以命中，如截图
image.png

查看是否能命中

explain select
F1.BQO AS BQO,
F1.dt AS dt,
sum(F1.O_ORD_00014) AS O_ORD_00014,
sum(F1.O_ORD_00061) AS O_ORD_00061,
sum(F1.O_ORD_00020) AS O_ORD_00020,
sum(F1.O_ORD_00001) AS O_ORD_00001,
sum(F1.O_ORD_00336) AS O_ORD_00336,
sum(F1.O_ORD_00065) AS O_ORD_00065,
sum(F1.O_ORD_00066) AS O_ORD_00066,
count(distinct F1.O_ORD_00021) AS O_ORD_00021
,count(*)
from
(
select
vf.businessline as ABA,
vf.dim_order_source_channel_category_lvl1_name as AFM,
if(
coalesce(concat('', vf.dim_analysis_business), '') in('', 'null'),
'(null)',
concat('', vf.dim_analysis_business)
) as BQO,
vf.is_2c_order_pay as CBT,
vf.dim_cost_businessline as CGW,
vf.dim_sale_mainbody_type as CMB,
substring(vf.dt, 1, 10) as _com_d,
substring(vf.dt, 1, 10) as dt,
vf.amt_a2 as O_ORD_00001,
vf.pay_gmv as O_ORD_00014,
vf.finish_order_gmv as O_ORD_00020,
vf.finish_order_user as O_ORD_00021,
vf.finish_order_orderid as O_ORD_00022,
vf.amt_b0 as O_ORD_00061,
vf.gross_profit_no_service_a15 as O_ORD_00065,
vf.gross_profit_no_service_a15 as O_ORD_00065_avg_by_d,
vf.gross_profit_no_service_a16 as O_ORD_00066,
vf.gross_profit_no_service_a16 as O_ORD_00066_avg_by_d,
vf.amt_a15 as O_ORD_00336
from
bi_test.ads_ord_order_detail_dtl_di vf
WHERE
vf.businessline IN ('保养', '洗美', '轮胎')
AND vf.dim_order_source_channel_category_lvl1_name IN ('线上自有平台', '线下门店')
AND vf.dt between '2025-12-01'
and '2025-12-31'
) F1
group by GROUPING SETS (
(F1.BQO ,F1.dt),(F1.dt)
)

无法命中 ，如图：
image.png

wangwenbo11
Related Questions
创建异步物化视图视图后发现无法命中
2 answers
paimon  分区物化视图使用分区上卷创建失败
1 answers
Paimon异步物化视图刷新失败
2 answers
paimon外表catalog没法创建异步物化视图 Only support creating asynchronous materialized views in internal catalog
1 answers