sql - Performance of regexp_replace vs translate in Oracle?

Question

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?

This question comes forth from How can I replace brackets to hyphens within Oracle REGEXP_REPLACE function?

Answer 1

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.

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

on 11.2.0.3.0 :

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

Conclusion: In general I suspect translate will win.