regex - Oracle - 当只对不同的匹配感兴趣时，优化 CLOB 列上所有正则表达式匹配的循环

Question

我正在制作一个循环遍历表(具有数千行)的存储过程，并且对于每一行都有一个 CLOB 列，我想从中获取正则表达式(sa“FNR”)上的所有匹配项。此后，我想在新表中插入每个 distinct 匹配项。单个 CLOB 列可能包含数千个匹配项，但通常它是在 CLOB 中重复的相同“FNR”——即不同的正则表达式匹配项要少得多，而这些是我唯一感兴趣的。但是，我执行的过程花费的时间长得离谱，而且我怀疑遍历所有匹配项是最耗时的部分。

我的程序是这样的:

CREATE OR REPLACE PROCEDURE SP_MTV_FINN_FNR AS
BEGIN
DECLARE 
  v_n NUMBER;
  v_cnt NUMBER;
  v_mtrid NUMBER;
  v_regex_fnr VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
  v_doc CLOB;
  v_fnr VARCHAR2(11);

  BEGIN
    -- Get all rows from table --
    SELECT COUNT(*) INTO v_n FROM TABLE;
    IF v_n > 0 THEN
      -- Loop over all rows --
      FOR i IN 1..v_n LOOP
        SELECT doc, mtrid
          INTO v_doc, v_mtrid
          FROM (SELECT DOC doc, ID mtrid, ROWNUM rnum
            FROM TABLE
            WHERE ROWNUM <=i)
          WHERE rnum >= i;
        IF v_doc IS NOT NULL THEN
          SELECT REGEXP_COUNT(v_doc, v_regex_fnr) INTO v_cnt FROM DUAL;
          IF v_cnt >= 1 THEN
            -- For each regex match - time consuming, right? --
            FOR j IN 1..v_cnt LOOP
              SELECT REGEXP_SUBSTR(v_doc, v_regex_fnr, 1, j, 'm') INTO v_fnr FROM DUAL;
                IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                  INSERT INTO TABLE2(MTR_ID, FNR)
                    SELECT v_mtrid, v_fnr FROM DUAL;
                END IF;
            END LOOP;
          END IF;
        END IF;
        COMMIT;
      END LOOP;
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error - rollback');
  DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
  ROLLBACK;
END;

/

有人知道如何优化这个过程吗？

我使用的是 Oracle 11.2.0.3.0。(顺便说一句，我知道 ctx_entity-package，但它在这个版本上被禁用了。不过，我正在考虑启用它。)

更新

应用 nop77svk 提供的非常有用的性能优化技术后，我可以肯定地说 CLOB 上的 regexp_substr() 是瓶颈，因为不幸的是没有性能提升。然而，我想出了一个“hack/workaround”，我最大限度地减少了 regexp_substr() 调用的数量，并获得了巨大的性能改进。首先，我想制作一个增量“训练有素”的正则表达式，排除以前的匹配项，但由于 Oracle 不支持否定前瞻，所以这行不通。我最终保存了 CLOB，并使用 regexp_replace() 删除了所有匹配项。由于 CLOB 中有很多相同的事件，这使该过程免于大量 regexp_substr() 调用，并且它同时处理了 distinct 要求。

下面是我的结果，基于 nop77svk 的贡献。是的，我又回到了在 MERGE 语句中使用 DUAL，但是这里有什么解决办法吗？

CREATE OR REPLACE PROCEDURE SP_MTV_FINN_FNR2 AS
BEGIN
DECLARE 
v_regex_fnr                 VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
v_fnr                       VARCHAR2(11);

v_doc CLOB;

type rec_table2             is record (
    mtr_id                      table2.mtr_id%type,
    fnr                         table2.fnr%type
);
type arr_table2             is table of rec_table2 index by simple_integer;
table2_bulk                 arr_table2;
table2_row                  rec_table2;
BEGIN
FOR rec IN (
    select doc, MTR_ID as mtrid
    from TABLE
    where DOC is not null
) LOOP
    v_doc := rec.doc;
    loop
        v_fnr := REGEXP_SUBSTR(v_doc, v_regex_fnr, 1, 1, 'm');
        exit when v_fnr is null;
        v_vedlegg := REGEXP_REPLACE(v_doc, v_fnr , '' , 1 , 0); -- Incrementally remove all occurences of match from doc --
        IF CHECK_FNR(v_fnr) = 'TRUE' THEN
            table2_row.mtr_id := rec.mtrid;
            table2_row.fnr := v_fnr;
            table2_bulk(table2_bulk.count+1) := table2_row;
        END IF;
    END LOOP;
END LOOP;
forall i in indices of table2_bulk
      MERGE INTO TABLE2 T
      USING (SELECT table2_bulk(i).mtr_id mtrid, table2_bulk(i).fnr fnr FROM DUAL) B
      ON (T.MTR_ID = B.mtrid AND T.FNR = B.fnr)
      WHEN NOT MATCHED THEN INSERT (T.MTR_ID, T.FNR)
      VALUES (B.mtrid, B.fnr);

COMMIT;
EXCEPTION
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error - rollback');
    DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
    ROLLBACK;
END;
END;
/

Answer 1

迭代调整您的 PL/SQL block ...

迭代 0: 修复语法错误 ...

CREATE OR REPLACE PROCEDURE SP_MTV_FINN_FNR AS
  v_n NUMBER;
  v_cnt NUMBER;
  v_mtrid NUMBER;
  v_regex_fnr VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
  v_doc CLOB;
  v_fnr VARCHAR2(11);

  BEGIN
    -- Get all rows from table --
    SELECT COUNT(*) INTO v_n FROM TABLE;
    IF v_n > 0 THEN
      -- Loop over all rows --
      FOR i IN 1..v_n LOOP
        SELECT doc, mtrid
          INTO v_doc, v_mtrid
          FROM (SELECT DOC doc, ID mtrid, ROWNUM rnum
            FROM TABLE
            WHERE ROWNUM <=i)
          WHERE rnum >= i;
        IF v_doc IS NOT NULL THEN
          SELECT REGEXP_COUNT(v_doc, v_regex_fnr) INTO v_cnt FROM DUAL;
          IF v_cnt >= 1 THEN
            -- For each regex match - time consuming, right? --
            FOR j IN 1..v_cnt LOOP
              SELECT REGEXP_SUBSTR(v_doc, v_regex_fnr, 1, j, 'm') INTO v_fnr FROM DUAL;
                IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                  INSERT INTO TABLE2(MTR_ID, FNR)
                    SELECT v_mtrid, v_fnr FROM DUAL;
                END IF;
            END LOOP;
          END IF;
        END IF;
        COMMIT;
      END LOOP;
    END IF;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Error - rollback');
  DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
  ROLLBACK;
END;

迭代 1: 删除不必要的 context switches和无用的行计数......

DECLARE 
    v_cnt NUMBER;
    v_regex_fnr VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
    v_fnr VARCHAR2(11);
BEGIN
    FOR rec IN (
        select doc, id as mtrid
        from table
    ) LOOP
        IF rec.doc IS NOT NULL THEN
            v_cnt := REGEXP_COUNT(rec.doc, v_regex_fnr);

            IF v_cnt >= 1 THEN
                -- For each regex match - time consuming, right? --
                FOR j IN 1..v_cnt LOOP
                    v_fnr := REGEXP_SUBSTR(rec.doc, v_regex_fnr, 1, j, 'm');

                    IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                        INSERT INTO TABLE2(MTR_ID, FNR) values (rec.mtrid, v_fnr);
                    END IF;
                END LOOP;
            END IF;
        END IF;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error - rollback');
        DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
        ROLLBACK;
END;
/

迭代 2: 减少外循环的数量 ...

DECLARE 
    v_cnt NUMBER;
    v_regex_fnr VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
    v_fnr VARCHAR2(11);
BEGIN
    FOR rec IN (
        select doc, id as mtrid
        from table
        where doc is not null
    ) LOOP
        v_cnt := REGEXP_COUNT(rec.doc, v_regex_fnr);

        IF v_cnt >= 1 THEN
            -- For each regex match - time consuming, right? --
            FOR j IN 1..v_cnt LOOP
                v_fnr := REGEXP_SUBSTR(rec.doc, v_regex_fnr, 1, j, 'm');

                IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                    INSERT INTO TABLE2(MTR_ID, FNR) values (rec.mtrid, v_fnr);
                END IF;
            END LOOP;
        END IF;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error - rollback');
        DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
        ROLLBACK;
END;
/

迭代 3: 缩短迭代 2 的代码 ...

DECLARE 
    v_regex_fnr VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
    v_fnr VARCHAR2(11);
BEGIN
    FOR rec IN (
        select doc, id as mtrid, REGEXP_COUNT(rec.doc, v_regex_fnr) as regexp_cnt
        from table
        where doc is not null
            and regexp_like(doc, v_regex_fnt)
    ) LOOP
        FOR j IN 1..rec.regexp_cnt LOOP
            v_fnr := REGEXP_SUBSTR(rec.doc, v_regex_fnr, 1, j, 'm');

            IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                INSERT INTO TABLE2(MTR_ID, FNR) values (rec.mtrid, v_fnr);
            END IF;
        END LOOP;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error - rollback');
        DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
        ROLLBACK;
END;
/

迭代 4: 删除不必要的 regexp_count() 计数 ...

DECLARE 
    v_regex_fnr VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
    v_fnr VARCHAR2(11);
    j integer;
BEGIN
    FOR rec IN (
        select doc, id as mtrid
        from table
        where doc is not null
    ) LOOP
        j := 1;
        loop
            v_fnr := REGEXP_SUBSTR(rec.doc, v_regex_fnr, 1, j, 'm');
            exit when v_fnt is null;

            IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                INSERT INTO TABLE2(MTR_ID, FNR) values (rec.mtrid, v_fnr);
            END IF;

            j := j + 1;
        END LOOP;
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error - rollback');
        DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
        ROLLBACK;
END;
/

迭代 5: 将结果保存到内存并立即将其刷新到 DB(使用集合绑定(bind))，加上处理 distinct 要求 ...

create or replace type obj_table2
as
object (
    mtr_id                      integer,
    fnr                         varchar2(4000)
);
/
create or replace type arr_table2
as
table of obj_table2;
/

DECLARE 
    v_regex_fnr                 VARCHAR2(54) := '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))';
    v_fnr                       VARCHAR2(11);
    j                           integer;

    table2_bulk                 arr_table2 := arr_table2();
BEGIN
    FOR rec IN (
        select doc, id as mtrid
        from table
        where doc is not null
    ) LOOP
        j := 1;
        loop
            v_fnr := REGEXP_SUBSTR(rec.doc, v_regex_fnr, 1, j, 'm');
            exit when v_fnt is null;

            IF CHECK_FNR(v_fnr) = 'TRUE' THEN
                table2_bulk.extend();
                table2_bulk(table2_bulk.last) := new obj_table2(
                    mtr_id => rec.mtrid,
                    fnr => v_fnr
                );
            END IF;

            j := j + 1;
        END LOOP;
    END LOOP;

    insert into table2(mtr_id, fnr)
    select mtr_id, fnr
    from table(table2_bulk) X
    minus
    select mtr_id, fnr
    from table2;

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error - rollback');
        DBMS_OUTPUT.PUT_LINE('The error code is ' || SQLCODE || '- ' || SQLERRM);
        ROLLBACK;
END;
/

迭代 6: 在决定 show off detestably 的同时将其全部丢弃...

insert into table2 (mtr_id, fnr)
with xyz (doc, mtrid, fnr, j) as (
    select doc, id as mtrid, cast(null as varchar2(4000)) as fnr, 0 as j
    from table A
    where doc is not null
    --
    union all
    --
    select doc, mtrid,
        regexp_substr(doc, '(((0[1-9]|[12]\d|3[01])(0[1-9]|1[012])(\d{2}))(\d{5}))', 1, j+1, 'm') as fnr,
        j+1
    from xyz X
    where j = 0
        or j > 0 and X.fnr is not null
)
select distinct mtrid, fnr
from xyz
where j > 0
    and fnr is not null
    and CHECK_FNR(fnr) = 'TRUE'
;
commit;

请注意，这些代码片段甚至可能不起作用。由于您没有向我们提供任何测试数据设置，我们只能以假设的方式调整您的代码。

请注意，其中最慢的部分仍然是 regexp_substr() 对 CLOB 值的处理。您可能想考虑使用 regexp_substr() 的 position 参数而不是 occurence 参数来获取后续的正则表达式匹配项。

享受吧。