Oracle生成带增量值重复行的查询多值场景下性能问题求助

当前SQL执行卡顿的核心问题是CONNECT BY子句缺少行级关联限定，会产生指数级的递归笛卡尔积：

• 未加PRIOR关联条件时，CONNECT BY会触发所有源行互相递归关联，若IN子句有N个唯一值，实际生成的行数远大于预期的N*5，会达到5^N量级。当N超过10时，数据量已达到千万级以上，再加上冗余的DISTINCT去重操作，就会出现执行数小时无返回的问题。
• 现有写法中的DISTINCT本身就是为了消除笛卡尔积产生的重复值，这种先爆量生成脏数据再去重的逻辑，本身就存在极大的性能浪费。

以下两种写法复杂度均为线性O(N)，不会随IN子句值的数量增加出现性能暴跌，哪怕IN子句包含上百个值也能毫秒级返回结果：

方案1：数字行交叉连接（可读性最高，最稳定） #

将生成5次序列的逻辑和业务数据查询逻辑拆分，通过交叉连接实现每行生成5条记录的需求：

SELECT 'Price Line ' || t.column1 || '-' || r.ranges AS Item_Number
FROM (
    SELECT DISTINCT column1 
    FROM tbl 
    WHERE column1 IN ('ABC','BCD','DEF','EFG','XYZ') -- 可随意扩展IN列表
) t
CROSS JOIN (
    SELECT level ranges 
    FROM dual 
    CONNECT BY level <= 5
) r
ORDER BY Item_Number;

方案2：改造CONNECT BY增加行隔离 #

给递归条件增加PRIOR SYS_GUID() IS NOT NULL限定，避免不同源行之间产生交叉递归：

SELECT 'Price Line ' || t.column1 || '-' || level AS Item_Number
FROM (
    SELECT DISTINCT column1 
    FROM tbl 
    WHERE column1 IN ('ABC','BCD','DEF','EFG','XYZ') -- 可随意扩展IN列表
) t
CONNECT BY PRIOR SYS_GUID() IS NOT NULL AND level <= 5
ORDER BY Item_Number;

内容的提问来源于stack exchange，提问作者Nagaraj Uppar