sql - 在 WHERE 子句中使用自定义函数加速更新

Question

我有一个函数，根据 wiki 页面 ( https://en.wikipedia.org/wiki/Check_digit) 检查输入的 ISBN 代码是否有效:

create or replace FUNCTION fn_isbn_valid (in_isbn IN VARCHAR2) RETURN VARCHAR2 
    IS
      l_isbn VARCHAR2(14);
      l_last_digit NUMBER(1);
      l_checksum NUMBER;

    BEGIN
      l_isbn := LPAD(in_isbn, 14, 0);
      l_last_digit := TO_NUMBER(SUBSTR(l_isbn, -1, 1));

      l_checksum := 
      ((10 - 
      ((
        3 * (
          TO_NUMBER(SUBSTR(L_isbn, 1, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 3, 1)) +
          TO_NUMBER(SUBSTR(L_isbn, 5, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 7, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 9, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 11, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 13, 1)))
        + 
          TO_NUMBER(SUBSTR(L_isbn, 2, 1)) +
          TO_NUMBER(SUBSTR(L_isbn, 4, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 6, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 8, 1)) + 
          TO_NUMBER(SUBSTR(L_isbn, 10, 1)) +
          TO_NUMBER(SUBSTR(L_isbn, 12, 1))   

      )mod 10 )) mod 10 ) ;  

      IF (l_checksum = l_last_digit) THEN
        RETURN 'Y';
      ELSE 
        RETURN 'N';
      END IF;  

    EXCEPTION
      WHEN VALUE_ERROR THEN
        RETURN 'N';

    END fn_isbn_valid;

然后我必须使用这个函数来更新我表中的指示器列:

    update my_table
        set isbn_valid_ind = 'N'
        where fn_isbn_valid(isbn) = 'N';

对于一个包含 2 万行无效 ISBN 编号的 10 万行表，更新大约需要 10 秒。

有什么提示或建议可以加快这个过程吗？谢谢。

Answer 1

我添加这个作为答案，只是为了我可以格式化，但我真的想详细说明我所做的评论。

我有一个“bigemp”表，180 万行。

20% 的行的值为 JOB = 'TEST'

如果我直接运行 SQL 语句:

 update bigemp
  2  set ename = lower(ename)
  3  where job = 'TEST'
  4  /

367001 rows updated.

Elapsed: 00:00:22.94

现在我有一个函数:

create or replace function is_valid( empno in number ) return varchar2
is
begin
  if mod( empno, 5 ) = 0
  then
    return 'N';
  else
    return 'Y';
  end if;
end;

现在我运行本质上相同的 SQL 语句，使用函数:

SQL> update bigemp
  2  set ename = upper(ename)
  3  where is_valid( empno ) = 'N'
  4  /

367001 rows updated.

Elapsed: 00:00:23.99

所以这两个语句都需要大约 23 秒。所以问题不是“加速功能”之一。

所以我们的寓意是，看看时间花在了哪里。