时间戳

首页 > 解决方案 > PL/SQL 加速长函数

regex - PL/SQL 加速长函数

问题描述

我希望加快以下 PL/SQL 函数的速度。现在它已经运行了2个多小时，没有完成的迹象。我们中止了那个并再次尝试了 EXIT WHEN 为 20，但它仍然没有显示实际完成的迹象。

我们通过 SQLDeveloper 17.3 运行这些，并且 (4) 个表中的每一个都有大约 15k 行。

目标是获取我们数据库中的所有 SSN，并将第一个字符更改为非法字符，将最后两个字符更改为随机 AZ 组合。然后，我们必须在使用它的每个表中更新该 SSN (4)。 

declare
    v_random varchar2(2); 
    v_origin_ssn varchar2(100);
    v_working_start varchar2(100);
    v_working_middle varchar2(100);
    v_new_ssn varchar2(100);
begin

    for o in (
        select distinct ssn  --loop all rows in tbl_customer
        from program_one.tbl_customer
    )
    loop
        if regexp_like(o.ssn, '^[A-Za-z9].*[A-Z]$') then continue; --if this is already scrambled, skip
        else 
            select dbms_random.string('U', 2) --create random 2 cap letters
                into v_random
            from dual;

            v_origin_ssn := o.ssn; --set origin ssn with the existing ssn

            if regexp_like(o.ssn, '^[A-Za-z]') --if first char is already alpha, leave it alone, otherwise 9
                then v_working_start := substr(o.ssn, 1, 1);
                else v_working_start := 9; 
            end if;

            v_working_middle := substr(o.ssn, 2, 6); --set middle ssn with the unchanged numbers

            v_new_ssn := v_working_start||v_working_middle||v_random; --create new sanitized ssn

            update program_one.tbl_customer --update if exists in tbl_customer
            set ssn = v_new_ssn
            where ssn = v_origin_ssn;

            commit;

            update program_one.tbl_mhc_backup --update if exists ssn tbl_mhc_backup
            set ssn = v_new_ssn
            where ssn = v_origin_ssn;

            commit;

            update program_two.tbl_waiver --update if exists ssn tbl_waiver
            set ssn = v_new_ssn
            where ssn = v_origin_ssn;

            commit;

            update program_two.tbl_pers --update if exists in tbl_pers
            set ssan = v_new_ssn
            where ssan = v_origin_ssn;

            commit;

        end if;

        --dbms_output.put_line(v_origin_ssn||' : '||v_new_ssn); --output test string to verify working correctly

    end loop;

end;

标签： regexoracleplsql

解决方案

我会在没有纯 SQL 的函数的情况下做到这一点：

用新旧 ssn 创建一个表：

CREATE TABLE tmp_ssn AS
  SELECT ssn, '9'||substr(ssn,2,6)||dbms_random.string('U',2) as new_ssn
    FROM (SELECT distinct ssn FROM program_one.tbl_customer);
CREATE UNIQUE INDEX ui_tmp_ssn ON tmp_ssn(ssn, new_ssn);
EXEC DBMS_STATS.GATHER_TABLE_STATS(null,'tmp_ssn');

...然后一一更新表格：

MERGE INTO program_one.tbl_customer z USING tmp_ssn q ON (z.ssn=q.ssn)
 WHEN MATCHED THEN UPDATE z.ssn = q.new_ssn;
COMMIT;

MERGE INTO program_one.tbl_mhc_backup z USING tmp_ssn q ON (z.ssn=q.ssn)
 WHEN MATCHED THEN UPDATE z.ssn = q.new_ssn;
COMMIT;

etc

如果这仍然很慢，我会做

RENAME tbl_customer to tbl_customer_old;
CREATE TABLE tbl_customer as
  SELECT s.new_ssn as ssn, t.col1, t.col2, ... , t.coln
    FROM tbl_customer_old t JOIN tmp_ssn s USING(ssn);
DROP TABLE tbl_customer_old;

推荐阅读