Used: 438105027512, Limit: 438103947386. Mem usage has exceed the limit of single query, You can change the limit by set session variable exec_mem_limit目前我设置比较这个限制的值大，还是报错超出限制，

💬 StarRocks 用户问答 功能使用相关
#222

image
image993×351 157 KB
卡了半个多小时了

#223

元数据太多， BUCKETS设置576试试，现在9个BE而已

#224

或者试一下 分区是 INTERVAL 1 MONTH

#225

MySQL [tcph10tmp]> select
-> s_name,
-> count(*) as numwait
-> from
-> supplier,
-> lineitem l1,
-> orders,
-> nation
-> where
-> s_suppkey = l1.l_suppkey
-> and o_orderkey = l1.l_orderkey
-> and o_orderstatus = ‘F’
-> and l1.l_receiptdate > l1.l_commitdate
-> and exists (
-> select
-> *
-> from
-> lineitem l2
-> where
-> l2.l_orderkey = l1.l_orderkey
-> and l2.l_suppkey <> l1.l_suppkey
-> )
-> and not exists (
-> select
-> *
-> from
-> lineitem l3
-> where
-> l3.l_orderkey = l1.l_orderkey
-> and l3.l_suppkey <> l1.l_suppkey
-> and l3.l_receiptdate > l3.l_commitdate
-> )
-> and s_nationkey = n_nationkey
-> and n_name = ‘MOZAMBIQUE’
-> group by
-> s_name
-> order by
-> numwait desc,
-> s_name
-> limit 100;

ERROR 1064 (HY000): row count of right table in hash join > 4294967295

#226

麻烦帮忙看一下这个sql

#227

这个能通过参数来调整吗

#228

那些表的数据量写一下

#229

order 150亿
lineitem 600亿
supplier 1亿
nation 25 条

#230

之前是sql 7 和 8 现在换了目前发现是sql 21 和5 有这个问题

#231

o_orderstatus = ‘F’，o_orderstatus可以建BITMAP索引

#232

SELECT
s_name,
count(*) AS numwait
FROM
lineitem l1,
orders,
supplier,
nation
WHERE
s_suppkey = l1.l_suppkey
AND o_orderkey = l1.l_orderkey
AND o_orderstatus = ‘F’
AND l1.l_receiptdate > l1.l_commitdate
AND EXISTS (
SELECT
*
FROM
lineitem l2
WHERE
l2.l_orderkey = l1.l_orderkey
AND l2.l_suppkey <> l1.l_suppkey
)
AND NOT EXISTS (
SELECT
*
FROM
lineitem l3
WHERE
l3.l_orderkey = l1.l_orderkey
AND l3.l_suppkey <> l1.l_suppkey
AND l3.l_receiptdate > l3.l_commitdate
)
AND s_nationkey = n_nationkey
AND n_name = ‘MOZAMBIQUE’
GROUP BY
s_name
ORDER BY
numwait DESC,
s_name
LIMIT 100;

改了一下表的顺序，看能不能跑

#233

嗯嗯好的我测试一次

#234

CREATE INDEX F on orders (o_orderstatus) USING BITMAP COMMENT ‘Bitmap Index’

CREATE INDEX index_name1 on orders (o_orderkey) USING BITMAP COMMENT ‘Bitmap Index’

#235

CREATE INDEX index_name1 on orders (o_orderkey) USING BITMAP COMMENT ‘Bitmap Index’
o_orderkey基数较大，不适合 BITMAP 。 基数少才合适。

#236 
-> ORDER BY
-> numwait DESC,
-> s_name
-> LIMIT 100;

ERROR 1064 (HY000): Column ‘‘F’’ cannot be resolved
MySQL [tcph10tmp]>
MySQL [tcph10tmp]>
MySQL [tcph10tmp]>
MySQL [tcph10tmp]> SELECT s_name, count(*) AS numwait FROM lineitem l1, orders, supplier, nation WHERE s_suppkey = l1.l_suppkey AND o_orderkey = l1.l_orderkey AND o_orderstatus = ‘F’ AND l1.l_receiptdate > l1.l_commitdate AND EXISTS ( SELECT * FROM lineitem l2 WHERE l2.l_orderkey = l1.l_orderkey AND l2.l_suppkey <> l1.l_suppkey ) AND NOT EXISTS ( SELECT * FROM lineitem l3 WHERE l3.l_orderkey = l1.l_orderkey AND l3.l_suppkey <> l1.l_suppkey AND l3.l_receiptdate > l3.l_commitdate ) AND s_nationkey = n_nationkey AND n_name = ‘MOZAMBIQUE’ GROUP BY s_name ORDER BY numwait DESC, s_name LIMIT 100;
ERROR 1064 (HY000): Column ‘‘F’’ cannot be resolved

#237

CREATE INDEX F on orders (o_orderstatus) USING BITMAP COMMENT ‘Bitmap Index’
名字不要建F，例如 bitmap_o_orderstatus
CREATE INDEX bitmap_o_orderstatus on orders (o_orderstatus) USING COMMENT ‘Bitmap Index’

#238

上面报错是执行调整顺序的sql报的错，
SELECT
s_name,
count(*) AS numwait
FROM
lineitem l1,
orders,
supplier,
nation
WHERE
s_suppkey = l1.l_suppkey
AND o_orderkey = l1.l_orderkey
AND o_orderstatus = ‘F’
AND l1.l_receiptdate > l1.l_commitdate
AND EXISTS (
SELECT
*
FROM
lineitem l2
WHERE
l2.l_orderkey = l1.l_orderkey
AND l2.l_suppkey <> l1.l_suppkey
)
AND NOT EXISTS (
SELECT
*
FROM
lineitem l3
WHERE
l3.l_orderkey = l1.l_orderkey
AND l3.l_suppkey <> l1.l_suppkey
AND l3.l_receiptdate > l3.l_commitdate
)
AND s_nationkey = n_nationkey
AND n_name = ‘MOZAMBIQUE’
GROUP BY
s_name
ORDER BY
numwait DESC,
s_name
LIMIT 100;
索引我是CREATE INDEX index_name1 on orders (o_orderstatus) USING BITMAP COMMENT ‘Bitmap Index’

#239

SQL里面的表 最好跟个别名，这样看起来思路也清晰。

#240

from 大表 右边是小表 您改一下吧

1赞
#241

这样的话sql逻辑是不是会变呢

京ICP备2022026421号-1