concat与||之间有性能差异吗?在甲骨文 [英] Is there performance difference between concat vs || in oracle

问题描述

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

I want to concat multiple(3) columns in oracle sql query. Currently I am using function concat. Someone suggested to use || in place of concat as it gives performance benefit.

是真的吗?如果是，为什么?

Is it true? If yes why?

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

I see only benefit of || is that written query more readable.

推荐答案

我在下面设置了一个简单的PL/SQL脚本，以尝试在一个循环中将两个串联选项分别进行1亿次. ||的结果为142.93秒，CONCAT的结果为144.11秒.无论哪种方式，您所说的每个操作大约需要1.4微秒.我的结论是，似乎没有明显的性能差异.

I set up a simple PL/SQL script(below) to try both concatenation options within a loop 100 million times each. The result for || was 142.93 seconds and CONCAT was 144.11 seconds. Either way, you're talking about roughly 1.4 microseconds per operation. My conclusion is that there doesn't appear to be any appreciable performance difference.

除了更具可读性之外，||是串联运算符的ANSI标准.

In addition to being more readable, || is the ANSI standard for the concatenation operator.

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功能:

---

As a footnote, Oracle says this about purpose of the CONCAT function:

在具有不同功能的系统之间移动SQL脚本文件时 字符集，例如在ASCII和EBCDIC之间，竖线可能 不能转换为目标Oracle所需的竖线 数据库环境. 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.

这篇关于concat与||之间有性能差异吗?在甲骨文的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！