Блог

У меня есть запрос, который требует, чтобы строки из таблиц были очищены от специальных символов перед их сравнением друг с другом. Я создал функцию, которая принимает строку и удаляет определенные специальные символы из строки перед ее возвратом. Проблема в том, что я обнаружил, что использую эту функцию много раз из-за того, что запрос выполняет много сравнений. Это значительно снизило производительность после добавления функциональности.

Итак, у меня есть эта функция, которую я создал:

 create or replace FUNCTION F_REMOVE_SPECIAL_CHARACTERS 
(
  IN_PARAM_EMAIL_NAME IN VARCHAR2,
  IN_PARAM_NUMBER_FLAG IN VARCHAR2 DEFAULT 'N'
) RETURN VARCHAR2 AS 
BEGIN
  /* If flag is Y then remove all numbers too. Otherwise, keep numbers in the string */
  IF IN_PARAM_NUMBER_FLAG = 'Y' THEN
    RETURN replace(regexp_replace(IN_PARAM_EMAIL_NAME, '[-,._0-9]', ''), ' ', '');
  ELSE
    RETURN replace(regexp_replace(IN_PARAM_EMAIL_NAME, '[-,._]', ''), ' ', '');
  END IF;
END F_REMOVE_SPECIAL_CHARACTERS;
  

У меня также есть запрос, который выглядит следующим образом:

 SELECT a.ID, LISTAGG(b.BUSINESS_EMAIL) WITHIN GROUP (ORDER BY a.ID)
FROM tableA a, tableB b
WHERE UPPER(F_REMOVE_SPECIAL_CHARACTERS(b.LAST_NAME)) IN (
  (SELECT UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.NICK_NAME)) FROM tableC c
      WHERE UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.NICK_NAME)) IN (
        (SELECT UPPER(F_REMOVE_SPECIAL_CHARACTERS(c.NAME)) FROM tableC c
           WHERE UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.NICK_NAME)) = UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.LAST_NAME))
           )
      )
  )
)
  

Фактический запрос больше и сложнее, но дело в том, что мне нужно удалить специальные символы из определенных значений столбцов, которые, случается, повторяются несколько раз в запросе. Это означает, что мне нужно использовать функцию несколько раз, но это приводит к значительному снижению производительности.

Есть ли у кого-нибудь идея о том, как уменьшить замедление производительности при использовании нескольких вызовов функций в запросе? Спасибо.

Предполагая, что вам это нужно как функция (потому что вы используете ее во многих местах), вы могли бы очистить ее и упростить (и сделать более эффективной) следующим образом:

 create or replace function f_remove_special_characters 
(
  in_param_email_name in varchar2,
  in_param_number_flag in varchar2 default 'N'
) 
return varchar2
deterministic
as 
pragma udf;   -- if on Oracle 12.1 or higher, and function is only for SQL use
  /* If flag is Y then remove all numbers too. 
     Otherwise, keep numbers in the string
  */
  chars_to_remove varchar2(16) := 'z-,._ ' || 
                         case in_param_number_flag when 'Y' then '0123456789' end;
begin
  return translate(in_param_email_name, chars_to_remove, 'z');
end f_remove_special_characters;
/
  

Глупый трюк с ‘z’ во translate (во втором и третьем аргументах) связан со странной обработкой Oracle null . В translate , если какой-либо из аргументов является null результатом является null , в отличие от обработки Oracle null в других строковых операциях.

Если вы используете 12c или выше, то в качестве быстрого исправления вы можете использовать предложение WITH FUNCTION

Насколько я помню, это устраняет переключение контекста PL / SQL<-> SQL, чтобы ваш запрос выполнялся лучше.

Я никогда не тестировал это, но очень вероятно, что это будет быстрее даже в 30-50 раз. Дайте мне знать, как быстро это будет, потому что мне любопытно

 WITH FUNCTION F_REMOVE_SPECIAL_CHARACTERS 
(
  IN_PARAM_EMAIL_NAME IN VARCHAR2,
  IN_PARAM_NUMBER_FLAG IN VARCHAR2 DEFAULT 'N'
) RETURN VARCHAR2 AS 
BEGIN
  /* If flag is Y then remove all numbers too. Otherwise, keep numbers in the string */
  IF IN_PARAM_NUMBER_FLAG = 'Y' THEN
    RETURN replace(regexp_replace(IN_PARAM_EMAIL_NAME, '[-,._0-9]', ''), ' ', '');
  ELSE
    RETURN replace(regexp_replace(IN_PARAM_EMAIL_NAME, '[-,._]', ''), ' ', '');
  END IF;
END;
SELECT a.ID, LISTAGG(b.BUSINESS_EMAIL) WITHIN GROUP (ORDER BY a.ID)
FROM tableA a, tableB b
WHERE UPPER(F_REMOVE_SPECIAL_CHARACTERS(b.LAST_NAME)) IN (
  (SELECT UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.NICK_NAME)) FROM tableC c
      WHERE UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.NICK_NAME)) IN (
        (SELECT UPPER(F_REMOVE_SPECIAL_CHARACTERS(c.NAME)) FROM tableC c
           WHERE UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.NICK_NAME)) = UPPER(F_REMOVE_SPECIAL_CHARACTERS(a.LAST_NAME))
           )
      )
  )
)