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?
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.