大神帮忙分析一个SQL语句看看问题在哪里，执行到超时

OceanBase 技术问题
,
#1

UPDATE communalgoodextend
SET GoodsSaleLevel = 2
WHERE
Id IN (
SELECT
GoodsId
FROM
(
SELECT
*,
@hnum := @hnum + 1 AS hnum
FROM
(
SELECT
*,
RANK() OVER ( PARTITION BY GoodClassId ORDER BY SaleNum ASC ) AS rownum
FROM
(
SELECT
a.GoodsId,
b.GoodClassId,
COALESCE ( SUM( a.SaleNum - a.SaleReturnNum ), 0 ) AS SaleNum
FROM
purchasestocksaledaily a
INNER JOIN communalgood b ON a.GoodsId = b.Id
WHERE
a.TenantId = 2
AND b.AffiliationBusinessformatId = ‘3a149ea7-b4fe-9fe0-dcd4-b26819688fa1’
AND a.ReportDate >= ‘2024-11-19’
AND a.ReportDate < ‘2024-12-19’
GROUP BY
a.GoodsId,
b.GoodClassId
) AS a,(
SELECT
@hnum := 0
) AS b
) AS c
ORDER BY
rownum
) AS d
WHERE
d.hnum <=(
@hnum * 0.1)
);

直接执行in里面的子查询很快，其实能查到的数据是0行，但是update语句一执行就卡住，一直执行到最大超时时间就挂了。

Oceanbase版本：5.7.25-OceanBase_CE-v4.3.0.1

表结构：
Oceanbase执行超时表结构.txt (16.4 KB)

我在一个新的数据库创建了表结构空数据下在执行这个update语句没有重现问题，很快就执行完。

数据量：
communalgoodextend：17920
communalgood：17920
purchasestocksaledaily：112194

#3

补充个信息，我用MySQL数据库在数据量等同的情况下执行很快就结束了

#4

能提供一个sql的文本么 不要带变量的

#5

没太明白，不带变量的SQL文本？ 那样的话就和这个SQL想要执行的业务不同了。就没法重现问题了

#6

SELECT *,RANK() OVER ( PARTITION BY GoodClassId ORDER BY SaleNum ASC ) AS rownum FROM
(SELECT a.GoodsId,b.GoodClassId,COALESCE ( SUM( a.SaleNum - a.SaleReturnNum ), 0 ) AS SaleNum
FROM purchasestocksaledaily a INNER JOIN communalgood b ON a.GoodsId = b.Id
WHERE a.TenantId = 2 AND b.AffiliationBusinessformatId = ‘3a149ea7-b4fe-9fe0-dcd4-b26819688fa1’ AND a.ReportDate >= ‘2024-11-19’ AND a.ReportDate < ‘2024-12-19’ GROUP BY a.GoodsId,b.GoodClassId
) AS a,(SELECT @hnum := 0) AS b;
(SELECT @hnum := 0) AS b 这个语句 我没理解 这个是给变量赋值么？这样写正确么？