Oracle 中 regexp_replace 与翻译的性能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16058279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance of regexp_replace vs translate in Oracle?
提问by Colin 't Hart
For simple things is it better to use the translatefunction on the premise that it is less CPU intensive or is regexp_replacethe way to go?
对于简单的事情，是translate在CPU占用较少的前提下使用该功能更好还是regexp_replace要走的路？
This question comes forth from How can I replace brackets to hyphens within Oracle REGEXP_REPLACE function?
回答by Vincent Malgrat
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.
我认为您遇到了简单的优化问题。regexp 表达式的计算成本非常高，结果被缓存，希望将来能再次使用。如果你真的使用不同的字符串来转换，你会发现适度的翻译自然更快，因为它是它的专门功能。
Here's my example, running on 11.1.0.7.0:
这是我的示例，运行于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:
上11.2.0.3.0：
regex :+000000000 00:00:00.617290000
translate :+000000000 00:00:00.138205000
Conclusion: In general I suspect translatewill win.
结论：总的来说，我怀疑translate会赢。
回答by Colin 't Hart
For SQL, I tested this with the following script:
对于 SQL，我使用以下脚本对此进行了测试：
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
)
);
and found that the performance of translateand regexp_replacewere almost always the same, but it could be that the cost of the other operations is overwhelming the cost of the functions I'm trying to test.
发现的性能translate和regexp_replace几乎都是一样的，但它可能是其他运营成本是压倒我想测试功能的成本。
Next, I tried a PL/SQL version:
接下来，我尝试了一个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;
/
Here the translateversion takes just under 10 seconds, while the regexp_replaceversion around 0.2 seconds -- around 2 orders of magnitude faster(!)
这里的translate版本只需要不到 10 秒，而regexp_replace版本大约需要 0.2 秒——快了大约 2 个数量级（！）
Based on this result, I will be using regular expressions much more often in my performance critical code -- both SQL and PL/SQL.
基于这个结果，我将在我的性能关键代码中更频繁地使用正则表达式——SQL 和 PL/SQL。
