我们正在使用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;
这些插件中哪个更好?
我想您是在问,因为您多次调用这段代码。通常,哪种方法更快,取决于您要插入的值在表中是否存在。与检查主键值尚不存在相比,引发和捕获异常要慢几个数量级。但是,如果每百万个插入仅抛出一次异常,则第二种方法可能会更有效。过去,当我遇到这样的事情时,就引发和捕获异常的代价而言,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
不再是预期的异常,因此您不应捕获并忽略它。