Warm tip: This article is reproduced from stackoverflow.com, please click
crud oracle performance sql-insert

Is the insert into ... where not exists costlier in performance?

发布于 2020-04-20 10:46:31

We are using Oracle database (12c)

A table ABCD has below structure with around o(10^5) rows

CCOL* - Varchar2 column

DCOL* - Timestamp column

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)

The primary key on this table is (CCOL1, CCOL2)

We have a below insert statement: Insert 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;

And alternative to this we have (removed the where not exists part)

Insert 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;

Which of these inserts is better?

Questioner
Abhijeet
Viewed
35
Justin Cave 2020-02-05 19:24

I assume you're asking because you call this piece of code a large number of times. Generally, which approach is faster will depend on how probable it is that the values you're inserting already exist in the table. Throwing and catching an exception is orders of magnitude slower than checking that a primary key value does not already exist. But if you're only going to throw the exception once on every million inserts, the second approach is likely to be more efficient. When I had something like this in the past, there were also significant differences between Oracle versions in terms of how much more costly it was to throw and catch exceptions so the exact break-even point will vary depending on the Oracle version as well. Realistically, you'd need to benchmark on your system to be sure.

Personally, I'd probably write this (as @Boneist suggest) as a MERGE with just a when not matched clause

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, ... )

Separately, having a WHEN OTHERS exception handler that just does a RAISE doesn't make much sense-- just don't catch the exception if you aren't going to do anything with it. In your first piece of code, it doesn't make sense to catch and ignore the dup_val_on_index check because you've already got the NOT EXISTS clause. At that point, the dup_val_on_index is no longer an expected exception so you shouldn't be catching and ignoring it.