Sequence Behavior with Multitable Insert All

Some times it gets little tricky when developers have some assumptions and they code as per them, but the output is little different. Multitable INSERT with SEQUENCE comes the same world of assumptions. Developers should remember that Sequences may not behave same with INSERT ALL as they might have assumed.

Let's do a little demo. We have EMP table as following

nimish@garg> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

I want to insert above data in 2 tables with following rule
1) Data of deptno = 20 should go in emp_dept20
2) Data having sal >= 2500 should go in emp_sal_2500

Following are the tables and sequence creation script

nimish@garg> create table emp_dept20 as 
  2  select empno, ename, sal, deptno, cast(null as number(10)) seq from emp where 1=2;

Table created.

nimish@garg> create table emp_sal_2500 as
  2  select empno, ename, sal, deptno, cast(null as number(10)) seq from emp where 1=2;

Table created.

nimish@garg> create sequence emp_seq;
Sequence created.

Above tables has a SEQ column, which will be pupulated by emp_seq sequence.

Now let us run the INSERT ALL statement which follows the above rules and check what data is inserted in SEQ column.

nimish@garg> insert all
  2     when deptno = 20 then
  3             into emp_dept20 (empno, ename, sal, deptno, seq)
  4             values (empno, ename, sal, deptno, emp_seq.nextval)
  5     when sal >= 2500 then
  6             into emp_sal_2500 (empno, ename, sal, deptno, seq)
  7             values (empno, ename, sal, deptno, emp_seq.nextval)
  8  select empno, ename, sal, deptno from emp;

10 rows created.

nimish@garg> select * from emp_dept20;

     EMPNO ENAME             SAL     DEPTNO        SEQ
---------- ---------- ---------- ---------- ----------
      7566 JONES            2975         20          4
      7788 SCOTT            3000         20          5
      7902 FORD             3000         20          6
      7369 SMITH             800         20          7
      7876 ADAMS            1100         20         12


nimish@garg> select * from emp_sal_2500;

     EMPNO ENAME             SAL     DEPTNO        SEQ
---------- ---------- ---------- ---------- ----------
      7839 KING             5000         10          1
      7698 BLAKE            2850         30          2
      7566 JONES            2975         20          4
      7788 SCOTT            3000         20          5
      7902 FORD             3000         20          6

If we look above data closely, especially the SEQ column, we observe following points
1) Only 7 rows has been used from EMP table and 10 rows inserted by INSERT ALL. (Same 3 rows got inserted in both tables)
2) SEQ column in both tables has same value for same row in both tables. So the take away here is If a record is inserted in multiple tables, only first reference to NEXTVAL generates the number.
3) Regardless the number of rows inserted, Sequence number is incremented by the number of rows which SELECT statement has fetched. That is the reason we have SEQ = 12 in "emp_dept20" table even if it has inserted only 7 unique rows.
4) If your data begin fetched from Select query is rejected by INSERT ALL conditions, it might result gaps in inserted data of sequence columns.

There are other restrictions as well on Multitable Insert, you can look into Oracle Documentation for this.

I hope you have enjoyed reading this post and might have learnt something new. Please post your feedback in comment box.

Related Articles:
- Performance Enhancements of SEQUENCE with each Oracle Version
- Sequence: NEXTVAL, CURRVAL and SESSION
- Setting Sequence CURRVAL to a Specific Number
- Oracle Auto Increment Column - Sequence as Default Value
- ORA-08002: sequence string.CURRVAL is not yet defined in this session