如何在Oracle中拆分区间字符串生成连续值并优化connect by慢查询问题

性能问题原因 #

原生CONNECT BY语法实现范围拆分时，递归生成行的过程会产生大量额外的计算、排序开销，数据量过万后性能下降非常明显。

方案1：递归CTE实现（无需额外对象，性能优于CONNECT BY 3~10倍） #

直接用Oracle 11gR2及以上版本支持的递归WITH子句实现，代码如下：

WITH test(col) AS (
    SELECT '01-06' FROM dual UNION ALL
    SELECT '45-52' FROM dual
),
-- 拆分每个范围的起止数值
range_info AS (
    SELECT
        TO_NUMBER(SUBSTR(col, 1, INSTR(col, '-') - 1)) AS start_n,
        TO_NUMBER(SUBSTR(col, INSTR(col, '-') + 1)) AS end_n
    FROM test -- 此处替换为实际业务表名
),
-- 递归生成连续数字
recur_gen(n, end_n) AS (
    SELECT start_n, end_n FROM range_info
    UNION ALL
    SELECT n + 1, end_n FROM recur_gen WHERE n < end_n
)
-- 补零格式化后输出
SELECT LPAD(n, 2, '0') AS COL FROM recur_gen
ORDER BY n;

方案2：数字辅助表实现（性能最优，适合高频使用场景） #

提前创建一张存储连续数字的辅助表，查询时直接关联，无递归开销，性能接近纯全表扫描：

步骤1：创建并初始化数字辅助表（一次创建，永久使用） #

-- 创建辅助表，主键自带索引
CREATE TABLE num_auxiliary (n NUMBER PRIMARY KEY);

-- 插入0~1000的连续数字，可根据业务最大范围调整上限
INSERT INTO num_auxiliary(n)
SELECT LEVEL - 1 FROM dual CONNECT BY LEVEL <= 1001;
COMMIT;

步骤2：关联查询实现拆分 #

WITH test(col) AS (
    SELECT '01-06' FROM dual UNION ALL
    SELECT '45-52' FROM dual
),
range_info AS (
    SELECT
        TO_NUMBER(SUBSTR(col, 1, INSTR(col, '-') - 1)) AS start_n,
        TO_NUMBER(SUBSTR(col, INSTR(col, '-') + 1)) AS end_n
    FROM test -- 此处替换为实际业务表名
)
SELECT LPAD(na.n, 2, '0') AS COL
FROM range_info ri
JOIN num_auxiliary na ON na.n BETWEEN ri.start_n AND ri.end_n
ORDER BY na.n;

优化提示 #

• 若不需要有序输出可删除ORDER BY子句，可进一步提升执行速度
• 若范围字符串的数字位数不固定，调整LPAD函数的第二个长度参数即可

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