oracle - concat 与 || 之间有性能差异吗在甲骨文中

Question

我想在 oracle sql 查询中连接多个 (3) 列。目前我正在使用函数concat。有人建议使用 || 代替 concat，因为它可以提高性能。

这是真的吗？如果是的话为什么？

我只看到 || 的好处书面查询更具可读性。

Answer 1

我设置了一个简单的 PL/SQL 脚本(如下)，在循环中尝试两个串联选项，每个选项 1 亿次。 || 的结果是 142.93 秒，CONCAT 的结果是 144.11 秒。不管怎样，每次操作大约需要 1.4 微秒。我的结论是，似乎没有任何明显的性能差异。

除了更具可读性之外，|| 是连接运算符的 ANSI 标准。

<小时/>

DECLARE
   i NUMBER;
   j NUMBER := 100000000;
   v VARCHAR2 (1000);
   v_start TIMESTAMP := SYSTIMESTAMP;
BEGIN
   FOR i IN 1 .. j LOOP
      v := DBMS_RANDOM.VALUE () || DBMS_RANDOM.VALUE ();
   END LOOP;    
   DBMS_OUTPUT.put_line ('1: ' || (SYSTIMESTAMP - v_start));
END;

DECLARE
   i NUMBER;
   j NUMBER := 100000000;
   v VARCHAR2 (1000);
   v_start TIMESTAMP := SYSTIMESTAMP;
BEGIN
   FOR i IN 1 .. j LOOP
      v := CONCAT (DBMS_RANDOM.VALUE (), DBMS_RANDOM.VALUE ());
   END LOOP;    
   DBMS_OUTPUT.put_line ('2: ' || (SYSTIMESTAMP - v_start));
END;

<小时/>

作为脚注，Oracle关于 CONCAT 函数的用途如下:

When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle Database environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets.