INSERT ALL with ID using DEFAULT ON NULL fails PK Constraint

Question

Using Oracle 12, I have a table defined similar to this:

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
)

When I do standard individual row inserts and leave off the ID, the sequence.nextval is called and the row is properly inserted. But we have to insert up to several thousand rows, so I am trying to use code like this:

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

When using INSERT ALL then the Primary Key constraint is violated.

We can switch back to the standard Trigger/Sequence pair, but were hoping to gain additional performance from using INSERT ALL.

Is there something special I have to do to get this bulk insert to work on a table with the key defined using DEFAULT ON NULL, or do I need to return to the old Trigger/Sequence pair?

Answer 1

It gets a bit more clear when you look at the query plan:

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

First Oracle runs the query (SELECT FROM dual), assigning the single id from the sequence and only after it goes through the results, inserting it to the tables. So this approach will not work.

You might try to use an interim table without the 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;

Something as simple as putting these records in a file and loading them via SQL Loader could work as well.