【详述】基于14亿的基表创建物化视图，create语句提示ok状态，show materilized views查看，发现物化视图刷新失败。
【背景】做过哪些操作？
【业务影响】
【StarRocks版本】3.0.3
【集群规模】例如：3fe+5be（32c、128G）
【机器信息】CPU虚拟核/内存/网卡，例如：32C/128G/万兆
【联系方式】378344307@qq.com
【执行语句】：
create materialized view gf_core.rtdw_dws_ivoucher_df_001
distributed by hash(year,month)
REFRESH ASYNC START(‘2023-07-12 18:00:00’) EVERY (interval 1 day)
as select
i.year ,
i.month,
i.keptbcode,
i.code00,
i.code01,
i.code07,
i.code08,
i.code09,
i.code04,
i.code16,
i.code19,
i.code14,
i.code02,
i.code03,
i.code06,
i.code11 ,
i.code15,
i.code13,
i.code24,
i.code23,
i.code18,
i.code20,
i.code12,
i.code27,
i.code25,
i.code21,
i.code26 ,
sum(i.scy*((i.vdc + 1)/ 2)) scydebit,
sum(i.scy*((1-i.vdc)/ 2)) scycredit,
sum(i.scyi.vdc) scybalance ,
sum(i.fcy((i.vdc + 1)/ 2)) fcydebit,
sum(i.fcy*((1-i.vdc)/ 2)) fcycredit,
sum(i.fcyi.vdc) fcybalance ,
sum(i.rcy2((i.vdc + 1)/ 2)) rcy2debit,
sum(i.rcy2*((1-i.vdc)/ 2)) rcy2credit,
sum(i.rcy2*i.vdc) rcy2balance
from
gf_core.ivoucher_df i
– left join acode a on i.code00=a.acode
– where
– pt=‘20230707’
– and i.code00 like ‘1%’
– and i.keptbcode=‘00004117’
– and (i.year<2020 or (i.year=2020 and i.month <=4))
group by i.year,
i.month,
i.keptbcode,
i.vdate,
i.code00,
i.code01,
i.code07,
i.code08,
i.code09,
i.code04,
i.code16,
i.code19,
i.code14,
i.code02,
i.code03,
i.code06,
i.code11,
i.code15,
i.code13,
i.code24,
i.code23,
i.code18,
i.code20,
i.code12,
i.code27,
i.code25,
i.code21,
i.code26 ;
【附件】
- 查询报错：
com.starrocks.common.UserException: Memory of Queryc6716add-209d-11ee-b8a2-005056954fda exceed limit. Pipeline Backend: starrocks04, fragment: c6716add-209d-11ee-b8a2-005056954fdd Used: 34359834124, Limit: 34359738368. Mem usage has exceed the limit of single query, You can change the limit by set session variable query_mem_limit.