regex - PL/SQL 优化在 varchar 中搜索日期

Question

我有一个表，其中包含日期字段(让它成为date s_date)和描述字段(varchar2(n) desc)。我需要的是编写一个脚本(或者一个查询，如果可能的话)，它将解析 desc 字段，如果它包含一个有效的 oracle 日期，那么它将剪切这个日期并更新 s_date，如果它是 null。

但还有一个条件 - desc 中的日期必须恰好一次出现。如果有 0 或 >1 - 不应更新任何内容。

当我想出这个使用正则表达式的丑陋解决方案时:

----------------------------------------------

create or replace function to_date_single( p_date_str in varchar2 )
    return date
is
    l_date date;
    pRegEx varchar(150);
    pResStr varchar(150); 
begin
    pRegEx := '((0[1-9]|[12][0-9]|3[01])[.](0[1-9]|1[012])[.](19|20)\d\d)((.|\n|\t|\s)*((0[1-9]|[12][0-9]|3[01])[.](0[1-9]|1[012])[.](19|20)\d\d))?';
    pResStr := regexp_substr(p_date_str, pRegEx);
    if not (length(pResStr) = 10)
    then return null;
    end if;
    l_date := to_date(pResStr, 'dd.mm.yyyy');
    return l_date;
exception
    when others then return null;
end to_date_single;

----------------------------------------------

update myTable t
set t.s_date = to_date_single(t.desc)
where t.s_date is null;

----------------------------------------------

但它的工作速度非常慢(每条记录超过一秒，我需要更新大约 30000 条记录)。是否可以以某种方式优化功能？也许这是在没有正则表达式的情况下做事的方法？还有其他想法吗？

感谢任何建议:)

编辑:

好的，也许它对某人有用。以下正则表达式执行有效日期 (DD.MM.YYYY) 的检查，同时考虑一个月中的天数，包括闰年检查:

(((0[1-9]|[12]\d|3[01])\.(0[13578]|1[02])\.((19|[2-9]\d)\d{2}))|((0[1-9]|[12]\d|30)\.(0[13456789]|1[012])\.((19|[2-9]\d)\d{2}))|((0[1-9]|1\d|2[0-8])\.02\.((19|[2-9]\d)\d{2}))|(29\.02\.((1[6-9]|[2-9]\d)(0[48]|[2468][048]|[13579][26])|((16|[2468][048]|[3579][26])00))))

我在@David 建议的查询中使用了它(请参阅已接受的答案)，但我尝试使用 select 而不是 update (因此每减少 1 个正则表达式行，因为我们不做 regexp_substr) 只是为了“基准测试”的目的。

数字在这里可能不会说明太多，因为这完全取决于硬件、软件和特定的数据库设计，但我花了大约 2 分钟来选择 36K 条记录。更新会慢一些，但我认为这仍然是一个合理的时间。

Answer 1

我会按照单个更新查询的方式重构它。

在 where 子句中使用两次 regexp_instr() 调用来查找第一次出现匹配而第二次不出现匹配的行，并使用 regexp_substr() 提取匹配字符以进行更新。

update my_table
set    my_date = to_date(regexp_subtr(desc,...),...)
where  regexp_instr(desc,pattern,1,1) > 0 and
       regexp_instr(desc,pattern,1,2) = 0

您可能会获得更好的性能:

update my_table
set    my_date = to_date(regexp_subtr(desc,...),...)
where  case regexp_instr(desc,pattern,1,1)
         when 0 then 'N'
         else case regexp_instr(desc,pattern,1,2)
           when 0 then 'Y'
           else 'N'
         end
       end = 'Y'

... 因为如果第一个正则表达式不为零，它只计算第二个正则表达式。第一个查询也可能这样做，但优化器可能会选择首先评估第二个谓词，因为它是一个相等条件，假设它更具选择性。

或者重新排序 Case 表达式可能会更好——这是一种难以判断的权衡，并且可能非常依赖于数据。