【详述】我现在有一张表，里面有十几个字段，我当前有一个慢SQL，对当前的慢sql建立了一个物化视图，包含了慢SQL所有的字段，并且我现在用 EXPLAIN SELECT发现命中了这个物化视图，但是报错了
【背景】SQL为select from where like order by 所有涉及的到字段我都在创建物化视图时加入了
【StarRocks版本】例如：1.19
【集群规模】3fe（3 follower）+3be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，28C/256G/百兆
【附件】
- 慢查询： EXPLAIN SELECT
obb.ROWID ,obb.BID_TITLE,obb.BID_TYPE,obb.BID_INDUSTRY_TYPE1,obb.PROVINCE,obb.CITY,obb.PUBLISH_TM,
obb.BID_BUDGET_AMT_SUM as BID_BUDGET_AMT,obb.WIN_AMT_SUM as WIN_AMT ,
obb.BID_STRUCT_TEXT,
obb.ATTACH_1,obb.ATTACH_1_COS,obb.ATTACH_1_NAME,
obb.ATTACH_2,obb.ATTACH_2_COS,obb.ATTACH_2_NAME,
obb.ATTACH_3,obb.ATTACH_3_COS,obb.ATTACH_3_NAME,
obb.ATTACH_4,obb.ATTACH_4_COS,obb.ATTACH_4_NAME,
obb.ATTACH_5,obb.ATTACH_5_COS,obb.ATTACH_5_NAME,
obb.ATTACH_6,obb.ATTACH_6_COS,obb.ATTACH_6_NAME,
obb.ATTACH_7,obb.ATTACH_7_COS,obb.ATTACH_7_NAME,
obb.ATTACH_8,obb.ATTACH_8_COS,obb.ATTACH_8_NAME,
obb.ATTACH_9,obb.ATTACH_9_COS,obb.ATTACH_9_NAME,
obb.ATTACH_10,obb.ATTACH_10_COS,obb.ATTACH_10_NAME
FROM ods_bid_list_publish obb
WHERE obb.BID_TEXT LIKE “%计算机%”
AND obb.BID_TYPE NOT IN (‘100’,‘117’)
AND obb.PUBLISH_TM IS NOT NULL
ORDER BY obb.PUBLISH_TM asc LIMIT 50
–物化视图sql
CREATE MATERIALIZED VIEW publish_test as SELECT
obb.ROWID ,obb.BID_TITLE,obb.BID_TYPE,obb.BID_INDUSTRY_TYPE1,obb.PROVINCE,obb.CITY,obb.PUBLISH_TM,
obb.BID_BUDGET_AMT_SUM as BID_BUDGET_AMT,obb.WIN_AMT_SUM as WIN_AMT,obb.BID_TEXT,
obb.BID_STRUCT_TEXT,
obb.ATTACH_1,obb.ATTACH_1_COS,obb.ATTACH_1_NAME,
obb.ATTACH_2,obb.ATTACH_2_COS,obb.ATTACH_2_NAME,
obb.ATTACH_3,obb.ATTACH_3_COS,obb.ATTACH_3_NAME,
obb.ATTACH_4,obb.ATTACH_4_COS,obb.ATTACH_4_NAME,
obb.ATTACH_5,obb.ATTACH_5_COS,obb.ATTACH_5_NAME,
obb.ATTACH_6,obb.ATTACH_6_COS,obb.ATTACH_6_NAME,
obb.ATTACH_7,obb.ATTACH_7_COS,obb.ATTACH_7_NAME,
obb.ATTACH_8,obb.ATTACH_8_COS,obb.ATTACH_8_NAME,
obb.ATTACH_9,obb.ATTACH_9_COS,obb.ATTACH_9_NAME,
obb.ATTACH_10,obb.ATTACH_10_COS,obb.ATTACH_10_NAME
FROM ods_bid_list_publish obb
—命中信息
INSERT INTO MY_TABLE(Explain String) VALUES (‘WORK ON CBO OPTIMIZER’);
INSERT INTO MY_TABLE(Explain String) VALUES (‘PLAN FRAGMENT 0’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ OUTPUT EXPRS:1: ROWID | 13: BID_TITLE | 17: BID_TYPE | 14: BID_INDUSTRY_TYPE1 | 18: PROVINCE | 19: CITY | 2: PUBLISH_TM | 180: BID_BUDGET_AMT_SUM | 181: WIN_AMT_SUM | 11: BID_STRUCT_TEXT | 137: ATTACH_1 | 138: ATTACH_1_COS | 278: ATTACH_1_NAME | 139: ATTACH_2 | 140: ATTACH_2_COS | 279: ATTACH_2_NAME | 141: ATTACH_3 | 142: ATTACH_3_COS | 280: ATTACH_3_NAME | 143: ATTACH_4 | 144: ATTACH_4_COS | 281: ATTACH_4_NAME | 145: ATTACH_5 | 146: ATTACH_5_COS | 287: ATTACH_5_NAME | 147: ATTACH_6 | 148: ATTACH_6_COS | 282: ATTACH_6_NAME | 149: ATTACH_7 | 150: ATTACH_7_COS | 283: ATTACH_7_NAME | 151: ATTACH_8 | 152: ATTACH_8_COS | 284: ATTACH_8_NAME | 153: ATTACH_9 | 154: ATTACH_9_COS | 285: ATTACH_9_NAME | 155: ATTACH_10 | 156: ATTACH_10_COS | 286: ATTACH_10_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ PARTITION: UNPARTITIONED’);
INSERT INTO MY_TABLE(Explain String) VALUES (’’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ RESULT SINK’);
INSERT INTO MY_TABLE(Explain String) VALUES (’’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ 3:MERGING-EXCHANGE’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ limit: 50’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ use vectorized: true’);
INSERT INTO MY_TABLE(Explain String) VALUES (’’);
INSERT INTO MY_TABLE(Explain String) VALUES (‘PLAN FRAGMENT 1’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ OUTPUT EXPRS:’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ PARTITION: RANDOM’);
INSERT INTO MY_TABLE(Explain String) VALUES (’’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ STREAM DATA SINK’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ EXCHANGE ID: 03’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ UNPARTITIONED’);
INSERT INTO MY_TABLE(Explain String) VALUES (’’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ 2:TOP-N’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | order by: <slot 1> 1: ROWID ASC’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | offset: 0’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | limit: 50’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | use vectorized: true’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | ‘);
INSERT INTO MY_TABLE(Explain String) VALUES (’ 1:Project’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 1> : 1: ROWID’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 2> : 2: PUBLISH_TM’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 137> : 137: ATTACH_1’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 138> : 138: ATTACH_1_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 11> : 11: BID_STRUCT_TEXT’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 139> : 139: ATTACH_2’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 140> : 140: ATTACH_2_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 13> : 13: BID_TITLE’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 141> : 141: ATTACH_3’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 14> : 14: BID_INDUSTRY_TYPE1’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 142> : 142: ATTACH_3_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 143> : 143: ATTACH_4’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 144> : 144: ATTACH_4_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 17> : 17: BID_TYPE’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 145> : 145: ATTACH_5’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 18> : 18: PROVINCE’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 146> : 146: ATTACH_5_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 19> : 19: CITY’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 147> : 147: ATTACH_6’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 148> : 148: ATTACH_6_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 149> : 149: ATTACH_7’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 278> : 278: ATTACH_1_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 150> : 150: ATTACH_7_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 279> : 279: ATTACH_2_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 151> : 151: ATTACH_8’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 280> : 280: ATTACH_3_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 152> : 152: ATTACH_8_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 281> : 281: ATTACH_4_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 153> : 153: ATTACH_9’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 282> : 282: ATTACH_6_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 154> : 154: ATTACH_9_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 283> : 283: ATTACH_7_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 155> : 155: ATTACH_10’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 284> : 284: ATTACH_8_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 156> : 156: ATTACH_10_COS’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 285> : 285: ATTACH_9_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 286> : 286: ATTACH_10_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 287> : 287: ATTACH_5_NAME’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 180> : 180: BID_BUDGET_AMT_SUM’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | <slot 181> : 181: WIN_AMT_SUM’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | use vectorized: true’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ | ‘);
INSERT INTO MY_TABLE(Explain String) VALUES (’ 0:OlapScanNode’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ TABLE: ods_bid_list_publish’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ PREAGGREGATION: ON’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ PREDICATES: 9: BID_TEXT LIKE ‘’%计算机%’’, 17: BID_TYPE NOT IN (’‘100’’, ‘‘117’’), 2: PUBLISH_TM IS NOT NULL’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ partitions=1/1’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ rollup: publish_test’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ tabletRatio=10/10’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ tabletList=1252515,1252519,1252523,1252527,1252531,1252535,1252539,1252543,1252547,1252551’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ cardinality=1237500’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ avgRowSize=41.0’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ numNodes=0’);
INSERT INTO MY_TABLE(Explain String) VALUES (’ use vectorized: true’);
··报错信息
[42000][1064] Memory exceed limit. ChunksSorter Backend: 172.16.42.43, fragment: f4e3fcd4-8d43-11ec-9540-a0b3cc1d1379 Used: 2178421687, Limit: 2147483648. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit.
