Rendimiento de regexp_replace vs translate en Oracle?

Para cosas simples, ¿es mejor usar la function de translate bajo la premisa de que requiere less CPU o regexp_replace el path a seguir?

Esta pregunta surge de ¿Cómo puedo replace los corchetes por guiones dentro de la function REGEXP_REPLACE de Oracle?

Creo que te encuentras con una optimization simple. La expresión de expresiones regulares es tan costosa que el resultado se almacena en la memory caching con la esperanza de que se vuelva a utilizar en el futuro. Si realmente usa distintas cadenas para convertir, verá que la traducción modesta es naturalmente más rápida porque es su function especializada.

Aquí está mi ejemplo, ejecutándose el 11.1.0.7.0 :

 SQL> DECLARE 2 TYPE t IS TABLE OF VARCHAR2(4000); 3 lt; 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 

en 11.2.0.3.0 :

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

Conclusión: en general, sospecho que el translate ganará.

Para SQL, probé esto con el siguiente script:

 set timing on select sum(length(x)) from ( select translate('(<FIO>)', '()[]', '----') x from ( select * from dual connect by level <= 2000000 ) ); select sum(length(x)) from ( select regexp_replace('[(<FIO>)]', '[\(\)\[]|\]', '-', 1, 0) x from ( select * from dual connect by level <= 2000000 ) ); 

y descubrió que el performance de translate y regexp_replace era casi siempre el mismo, pero podría ser que el costo de las otras operaciones sea abrumador en el costo de las funciones que estoy tratando de probar.

A continuación, probé una versión PL / SQL:

 set timing on declare x varchar2(100); begin for i in 1..2500000 loop x := translate('(<FIO>)', '()[]', '----'); end loop; end; / declare x varchar2(100); begin for i in 1..2500000 loop x := regexp_replace('[(<FIO>)]', '[\(\)\[]|\]', '-', 1, 0); end loop; end; / 

Aquí, la versión de translate demora poco less de 10 segundos, mientras que la versión de regexp_replace alnetworkingedor de 0.2 segundos, alnetworkingedor de 2 órdenes de magnitud más rápido (!)

En base a este resultado, usaré expresiones regulares con mucha más frecuencia en mi código crítico de performance, tanto SQL como PL / SQL.