温馨提示:本文翻译自stackoverflow.com,查看原文请点击:oracle - Is the insert into ... where not exists costlier in performance?
crud oracle performance sql-insert

oracle - 插入...的性能不高吗?

发布于 2020-04-20 14:16:39

我们正在使用Oracle数据库(12c)

ABCD具有以下结构,具有大约o(10 ^ 5)行

CCOL *-Varchar2列

DCOL *-时间戳列

Name   Null?    Type              
----- -------- ----------------- 
CCOL1 NOT NULL VARCHAR2(64 CHAR) 
CCOL2 NOT NULL VARCHAR2(30 CHAR) 
CCOL3 NOT NULL VARCHAR2(64 CHAR) 
DCOL1 NOT NULL TIMESTAMP(6)      
CCOL4 NOT NULL VARCHAR2(64 CHAR) 
DCOL2 NOT NULL TIMESTAMP(6)      
CCOL5          VARCHAR2(32 CHAR)

该表上的主键是(CCOL1,CCOL2)

我们在下面有一个插入语句:插入1:

BEGIN
    INSERT INTO abcd (
        ccol1, ccol2, ccol3, dcol1, ccol4, dcol2, ccol5)
        SELECT
            :b1, :b2, :b3, :b4, :b5, :b6, :b7
        FROM
            dual
        WHERE
            NOT EXISTS (
                SELECT 1 FROM abcd  WHERE ccol1 = :b1 AND ccol2 = :b2 );
EXCEPTION
    WHEN dup_val_on_index THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
END;

除此之外,我们(删除了不存在的部分)

插入2:

BEGIN
    INSERT INTO abcd (
        ccol1, ccol2, ccol3, dcol1, ccol4, dcol2, ccol5)
        VALUES 
        (:b1, :b2, :b3, :b4, :b5, :b6, :b7);

EXCEPTION
    WHEN dup_val_on_index THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
END;

这些插件中哪个更好?

查看更多

提问者
Abhijeet
被浏览
34
Justin Cave 2020-02-05 19:24

我想您是在问,因为您多次调用这段代码。通常,哪种方法更快,取决于您要插入的值在表中是否存在。与检查主键值尚不存在相比,引发和捕获异常要慢几个数量级。但是,如果每百万个插入仅抛出一次异常,则第二种方法可能会更有效。过去,当我遇到这样的事情时,就引发和捕获异常的代价而言,Oracle版本之间也存在重大差异,因此确切的收支平衡点也将随Oracle版本而变化。实际上,您需要确定系统的基准。

就个人而言,我可能会这样写(如@Boneist建议),MERGE其中仅包含一个when not matched子句

MERGE INTO abcd dest
  USING( SELECT :b1 ccol1, :b2 ccol2, :b3 ccol3, ...
           FROM dual ) src
     ON( src.ccol1 = dest.ccol1 AND
         src.ccol2 = dest.ccol2 )
 WHEN NOT MATCHED THEN
    INSERT( ccol1, ccol2, ccol3, ... )
      VALUES( src.ccol1, src.ccol2, src.ccol3, ... )

另外,拥有一个WHEN OTHERS仅执行a 异常处理程序RAISE没有多大意义-如果您不打算对其进行任何处理,则不要捕获该异常。在您的第一段代码中,没有必要抓住并忽略该dup_val_on_index检查,因为您已经有了该NOT EXISTS子句。到那时,dup_val_on_index不再是预期的异常,因此您不应捕获并忽略它。