使用 默认值插入所有带 ID 的 空值失败 PK 约束

Question

使用 Oracle 12，我定义了一个类似于以下内容的表：

CREATE TABLE example 
(   "ID" NUMBER(*,0) DEFAULT ON NULL ex_seq.nextval NOT NULL ENABLE, 
"SIG_BOOK" NUMBER(10,0), 
"SIG_LINE" NUMBER(10,0), 
"TRANSFER" NUMBER(10,0) DEFAULT NULL, 
CONSTRAINT "PK_EXAMPLE_ID" PRIMARY KEY ("ID")
-- snipped
)

当我执行标准的单个行插入并省略 ID 时，将调用sequence.nextval并正确插入该行。但是我们必须插入多达几千行，所以我尝试使用这样的代码：

INSERT ALL
INTO example (sig_book, sig_line, transfer) VALUES (1,22000006,3436440)
INTO example (sig_book, sig_line, transfer) VALUES (1,22000006,3184718)
SELECT * FROM dual

使用INSERT ALL时，将违反主键约束。

我们可以切换回标准的触发器/序列对，但希望通过使用INSERT ALL获得额外的性能。

我必须做一些特别的事情才能让这个批量插入在具有使用DEFAULT ON NULL定义的键的表上工作，还是我需要返回到旧的触发器/序列对？

Answer 1

当您查看查询计划时，它会变得更加清晰：

-----------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | INSERT STATEMENT    |         |     1 |     2   (0)| 00:00:01 |
|   1 |  MULTI-TABLE INSERT |         |       |            |          |
|   2 |   SEQUENCE          | EX_SEQ  |       |            |          |
|   3 |    FAST DUAL        |         |     1 |     2   (0)| 00:00:01 |
|   4 |   INTO              | EXAMPLE |       |            |          |
|   5 |   INTO              | EXAMPLE |       |            |          |
-----------------------------------------------------------------------

首先，Oracle运行查询(SELECT FROM dual(，从序列中分配单个id，只有在它通过结果之后，才将其插入到表中。所以这种方法是行不通的。

您可以尝试使用不带 id 的临时表：

CREATE TABLE example_without_id ("SIG_BOOK" NUMBER(10,0), ... );
INSERT ALL
INTO example_without_id (sig_book, sig_line, transfer) VALUES (1,22000006,3436440)
INTO example_without_id (sig_book, sig_line, transfer) VALUES (1,22000006,3184718)
SELECT * FROM dual;
INSERT INTO example (sig_book, sig_line, transfer) SELECT * FROM example_without_id;

像将这些记录放在文件中并通过SQL加载器加载它们这样简单的事情也可以工作。

Answer 2

改用INSERT INTO . . . SELECT怎么样？

INSERT INTO example (sig_book, sig_line, transfer) 
SELECT 1,22000006,3436440 FROM DUAL UNION ALL
SELECT 1,22000006,3184718 FROM DUAL;