regexp_replace的性能与在Oracle中翻译的性能有关? [英] Performance of regexp_replace vs translate in Oracle?

问题描述

对于简单的事情，最好在不占用CPU较多或regexp_replace可行的前提下使用translate函数?

For simple things is it better to use the translate function on the premise that it is less CPU intensive or is regexp_replace the way to go?

此问题来自如何在Oracle REGEXP_REPLACE函数中将括号替换为连字符?

推荐答案

我认为您正在遇到简单的优化. regexp表达式的计算是如此昂贵，以至于结果将被缓存，以期将来再次使用.如果您实际上使用不同的字符串进行转换，您会发现适度的翻译自然是更快的，因为它是其专门功能.

I think you're running into simple optimization. The regexp expression is so expensive to compute that the result is cached in the hope that it will be used again in the future. If you actually use distinct strings to convert, you will see that the modest translate is naturally faster because it is its specialized function.

这是我的示例，在11.1.0.7.0上运行:

Here's my example, running on 11.1.0.7.0:

SQL> DECLARE
  2     TYPE t IS TABLE OF VARCHAR2(4000);
  3     l       t;
  4     l_level NUMBER := 1000;
  5     l_time  TIMESTAMP;
  6     l_char  VARCHAR2(4000);
  7  BEGIN
  8     -- init
  9     EXECUTE IMMEDIATE 'ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2';
 10     SELECT dbms_random.STRING('p', 2000)
 11       BULK COLLECT
 12       INTO l FROM dual
 13     CONNECT BY LEVEL <= l_level;
 14     -- regex
 15     l_time := systimestamp;
 16     FOR i IN 1 .. l.count LOOP
 17        l_char := regexp_replace(l(i), '[]()[]', '-', 1, 0);
 18     END LOOP;
 19     dbms_output.put_line('regex     :' || (systimestamp - l_time));
 20     -- tranlate
 21     l_time := systimestamp;
 22     FOR i IN 1 .. l.count LOOP
 23        l_char := translate(l(i), '()[]', '----');
 24     END LOOP;
 25     dbms_output.put_line('translate :' || (systimestamp - l_time));
 26  END;
 27  /

regex     :+000000000 00:00:00.979305000
translate :+000000000 00:00:00.238773000

PL/SQL procedure successfully completed

在11.2.0.3.0上:

regex     :+000000000 00:00:00.617290000
translate :+000000000 00:00:00.138205000

结论:一般来说，我怀疑translate会赢.

Conclusion: In general I suspect translate will win.

这篇关于regexp_replace的性能与在Oracle中翻译的性能有关?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！