PLSQL中从CLOB列拆分员工列表到新表执行过慢问题求助

我有TableA和TableB两张表，结构如下：

TableA 建表语句

create table tableA
(
    Job_id varchar2(50),
    Job_type varchar2(50),
    Employee_list clob,
    current_date date,
    salary varchar2(15)
);

其中Employee_list为CLOB类型，存储的员工列表字符串长度超过4000字符，以分号作为分隔符。

TableB 建表语句

create table tableB
(
    Job_id varchar2(50),
    Employee varchar2(20),
    current_date date,
    salary varchar2(15)
); 

需求是将TableA中job_type = 'Managerial'且current_date = sysdate的数据，拆分Employee_list为单行员工姓名后插入到TableB中。我使用了以下SQL，但执行数小时仍未完成：

INSERT INTO tableB (job_id, employee, current_date,salary)
SELECT job_id,
       REGEXP_SUBSTR(employee_list, '[^;]+', 1, LEVEL) as employee, current_date,salary
FROM (
select job_id,employee_list,current_date,salary from
tableA where 
job_type = 'Managerial' and current_date = sysdate  
)
CONNECT BY job_id = PRIOR job_id AND PRIOR SYS_GUID() IS NOT NULL
  AND LEVEL <= REGEXP_COUNT(employee_list, '[^;]+');

1. 预计算拆分次数，减少递归重复计算 #

原SQL的CONNECT BY逻辑会反复计算REGEXP_COUNT和REGEXP_SUBSTR，针对大CLOB开销极高。可以先预计算每条记录的拆分次数，再通过数字序列JOIN实现拆分，避免递归重复运算：

WITH preprocessed AS (
    SELECT 
        job_id,
        employee_list,
        current_date,
        salary,
        REGEXP_COUNT(employee_list, '[^;]+') AS split_count
    FROM tableA 
    WHERE job_type = 'Managerial' 
      AND TRUNC(current_date) = TRUNC(sysdate) -- 若current_date带时分秒，用TRUNC匹配日期部分；否则保留原条件
),
numbers AS (
    SELECT LEVEL AS n
    FROM dual
    CONNECT BY LEVEL <= (SELECT MAX(split_count) FROM preprocessed)
)
INSERT INTO tableB (job_id, employee, current_date, salary)
SELECT 
    p.job_id,
    REGEXP_SUBSTR(p.employee_list, '[^;]+', 1, n.n) AS employee,
    p.current_date,
    p.salary
FROM preprocessed p
JOIN numbers n ON n.n <= p.split_count
WHERE REGEXP_SUBSTR(p.employee_list, '[^;]+', 1, n.n) IS NOT NULL; -- 过滤列表首尾分号产生的空值

2. 替换正则表达式，用DBMS_LOB处理大CLOB #

正则表达式对大CLOB的处理效率偏低，改用DBMS_LOB包的定位、截取函数，能大幅提升拆分速度：

WITH preprocessed AS (
    SELECT 
        job_id,
        employee_list,
        current_date,
        salary,
        -- 预计算拆分次数：分号数量+1
        DBMS_LOB.INSTR(employee_list, ';', 1, 1000000) = 0 
            ? DBMS_LOB.INSTR(employee_list, ';', 1, -1) + 1 
            : 1000000 -- 处理极端长列表
        AS split_count
    FROM tableA 
    WHERE job_type = 'Managerial' 
      AND current_date = sysdate
),
numbers AS (
    SELECT LEVEL AS n
    FROM dual
    CONNECT BY LEVEL <= (SELECT MAX(split_count) FROM preprocessed)
)
INSERT INTO tableB (job_id, employee, current_date, salary)
SELECT 
    p.job_id,
    TRIM(DBMS_LOB.SUBSTR(
        p.employee_list,
        NVL(DBMS_LOB.INSTR(p.employee_list, ';', 1, n.n), DBMS_LOB.GETLENGTH(p.employee_list)+1) 
        - NVL(DBMS_LOB.INSTR(p.employee_list, ';', 1, n.n-1), 0) - 1,
        NVL(DBMS_LOB.INSTR(p.employee_list, ';', 1, n.n-1), 0) + 1
    )) AS employee,
    p.current_date,
    p.salary
FROM preprocessed p
JOIN numbers n ON n.n <= p.split_count
WHERE TRIM(DBMS_LOB.SUBSTR(
        p.employee_list,
        NVL(DBMS_LOB.INSTR(p.employee_list, ';', 1, n.n), DBMS_LOB.GETLENGTH(p.employee_list)+1) 
        - NVL(DBMS_LOB.INSTR(p.employee_list, ';', 1, n.n-1), 0) - 1,
        NVL(DBMS_LOB.INSTR(p.employee_list, ';', 1, n.n-1), 0) + 1
    )) IS NOT NULL;

3. 临时表+批量插入优化 #

针对超大量数据，先将过滤后的TableA数据导入临时表，再批量拆分插入，减少原表锁竞争和查询开销：

-- 创建临时表
CREATE GLOBAL TEMPORARY TABLE temp_tableA_split (
    job_id varchar2(50),
    employee_list clob,
    current_date date,
    salary varchar2(15),
    split_count NUMBER
) ON COMMIT PRESERVE ROWS;

-- 插入预过滤数据
INSERT INTO temp_tableA_split
SELECT 
    job_id,
    employee_list,
    current_date,
    salary,
    REGEXP_COUNT(employee_list, '[^;]+') AS split_count
FROM tableA 
WHERE job_type = 'Managerial' 
  AND current_date = sysdate;

-- 启用直接路径插入，提升插入速度
INSERT /*+ APPEND */ INTO tableB (job_id, employee, current_date, salary)
SELECT 
    t.job_id,
    REGEXP_SUBSTR(t.employee_list, '[^;]+', 1, LEVEL) AS employee,
    t.current_date,
    t.salary
FROM temp_tableA_split t
CONNECT BY LEVEL <= t.split_count
  AND PRIOR t.job_id = t.job_id
  AND PRIOR SYS_GUID() IS NOT NULL;

COMMIT;

4. 索引优化 #

给TableA的过滤条件创建组合索引，快速定位目标记录，减少扫描范围：

CREATE INDEX idx_tableA_jobtype_date ON tableA (job_type, current_date);

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