Multi-Table Inserts with Sequences

Multi-table inserts are a drag when sequences are involved, because we don’t have enough control over when the sequence is incremented. I’ve seen some dangerous workarounds. Here is a workaround that I think is safe.

[Update  2016-02-12: paulzip caught me assuming the output would be ordered without an ORDER BY. I made the correction.]

DEPT and EMP from XML

Here is a snippet from a typical XML document with DEPT and EMP information.

...
<DEPTS>
    <DEPT>
        <DNAME>ACCOUNTING</DNAME>
        <LOC>NEW YORK</LOC>
        <EMPS>
            <EMP>
                <ENAME>CLARK</ENAME>
                <JOB>MANAGER</JOB>
                <HIREDATE>1981-06-09</HIREDATE>
                <SAL>2450</SAL>
            </EMP>
            <EMP>
                <ENAME>MILLER</ENAME>
                <JOB>CLERK</JOB>
                <HIREDATE>1982-01-23</HIREDATE>
                <SAL>1300</SAL>
            </EMP>
...
        </EMPS>
    </DEPT>
...
</DEPTS>

The fact that CLARK works in the ACCOUNTING department is indicated by the placement of CLARK inside the path of ACCOUNTING. (To simplify the problem, I leave out MGR.)

The following view will generate the XML content from the SCOTT schema, leaving out DEPTNO, EMPNO and MGR.

create or replace view dept_emp_xml as
with emps as (
  select deptno,
    xmlagg(
      xmlelement(
        "EMP",
        xmlforest(ENAME, JOB, HIREDATE, SAL, COMM)
      )
    ) emps
  from scott.emp
  group by deptno
)
select xmlelement(
  "DEPTS",
  xmlagg(
    xmlelement(
      "DEPT",
      xmlforest(DNAME, LOC, EMPS)
    )
  )
) depts
from scott.dept
left join emps using(deptno);

From XML to Relational

Let’s say I want to load this data to relational tables. I can use the XMLTABLE() function to convert the data to a result set:

create or replace view dept_emp_rel as
select deptno, DNAME, LOC, RN, 0 EMPNO, ENAME, JOB, HIREDATE, SAL, COMM
from dept_emp_xml a,
XMLTABLE(
  '$X/DEPTS/DEPT' PASSING DEPTS AS X
  COLUMNS
    deptno for ordinality,
    DNAME VARCHAR2(63) path 'DNAME',
    LOC VARCHAR2(63) path 'LOC',
    EMPS xmltype path 'EMPS'
) (+) b,
xmltable(
  '/EMPS/EMP' passing b.EMPS
  columns
    rn for ordinality,
    ENAME VARCHAR2(63) path 'ENAME',
    JOB VARCHAR2(63) path 'JOB',
    HIREDATE date path 'HIREDATE',
    SAL number path 'SAL',
    COMM number path 'COMM'
) (+) c;
DEPTNO DNAME LOC RN EMPNO ENAME JOB HIREDATE SAL COMM
1 ACCOUNTING NEW YORK 1 0 CLARK MANAGER 1981-06-09 2450
1 ACCOUNTING NEW YORK 2 0 MILLER CLERK 1982-01-23 1300
1 ACCOUNTING NEW YORK 3 0 KING PRESIDENT 1981-11-17 5000
2 RESEARCH DALLAS 1 0 SMITH CLERK 1980-12-17 800
2 RESEARCH DALLAS 2 0 FORD ANALYST 1981-12-03 3000
2 RESEARCH DALLAS 3 0 ADAMS CLERK 1987-05-23 1100
2 RESEARCH DALLAS 4 0 SCOTT ANALYST 1987-04-19 3000
2 RESEARCH DALLAS 5 0 JONES MANAGER 1981-04-02 2975
3 SALES CHICAGO 1 0 ALLEN SALESMAN 1981-02-20 1600 300
3 SALES CHICAGO 2 0 JAMES CLERK 1981-12-03 950
3 SALES CHICAGO 3 0 TURNER SALESMAN 1981-09-08 1500 0
3 SALES CHICAGO 4 0 BLAKE MANAGER 1981-05-01 2850
3 SALES CHICAGO 5 0 MARTIN SALESMAN 1981-09-28 1250 1400
3 SALES CHICAGO 6 0 WARD SALESMAN 1981-02-22 1250 500
4 OPERATIONS BOSTON 0

Loading the Result Set

Let’s imagine for a moment that the result set contains valid DEPTNO and EMPNO values. If that were true, we could use a multi-table insert to load the DEPT and EMP records. The important thing is to have the correct DEPTNO value in every row, so that the foreign key in EMP is correct.

drop table dept purge;
create table dept as
select * from scott.dept where 1=0;

drop table emp purge;
create table emp as
select EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO
from scott.emp
where 1=0;

insert all
when rn = 1 or rn is null then
  into dept(deptno, dname, loc)
     values(deptno, dname, loc)
when rn is not null then
  into emp(EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)
    values(EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)
select * from dept_emp_rel order by deptno, rn;

select * from dept
left join emp using(deptno)
order by deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB HIREDATE SAL COMM
1 ACCOUNTING NEW YORK 0 CLARK MANAGER 1981-06-09 2450
1 ACCOUNTING NEW YORK 0 KING PRESIDENT 1981-11-17 5000
1 ACCOUNTING NEW YORK 0 MILLER CLERK 1982-01-23 1300
2 RESEARCH DALLAS 0 SCOTT ANALYST 1987-04-19 3000
2 RESEARCH DALLAS 0 ADAMS CLERK 1987-05-23 1100
2 RESEARCH DALLAS 0 FORD ANALYST 1981-12-03 3000
2 RESEARCH DALLAS 0 SMITH CLERK 1980-12-17 800
2 RESEARCH DALLAS 0 JONES MANAGER 1981-04-02 2975
3 SALES CHICAGO 0 ALLEN SALESMAN 1981-02-20 1600 300
3 SALES CHICAGO 0 JAMES CLERK 1981-12-03 950
3 SALES CHICAGO 0 TURNER SALESMAN 1981-09-08 1500 0
3 SALES CHICAGO 0 BLAKE MANAGER 1981-05-01 2850
3 SALES CHICAGO 0 MARTIN SALESMAN 1981-09-28 1250 1400
3 SALES CHICAGO 0 WARD SALESMAN 1981-02-22 1250 500
4 OPERATIONS BOSTON

Handling the Sequence!

The problem with sequences in my INSERT ALL statement is that any sequence will be incremented for every row returned from the SELECT. This is the problem people try to work around. Often, the idea is to increment the sequence inside a function. Unfortunately, there is never a guarantee that the function will be called exactly when we want.

My suggestion is to use a pipelined table function, which gives us complete control over when the sequence is incremented. The function takes the result of the XMLTABLE() query and manages the sequences as follows:

  1. When the incoming DEPTNO value changes, increment the DEPT sequence.
  2. When RN is not null, increment the EMP sequence.

The output of the function is identical to the input, except that DEPTNO and EMPNO are updated from the sequences.

drop sequence dept_seq;
create sequence dept_seq;

drop sequence emp_seq;
create sequence emp_seq;

create or replace package dept_emp as
  type tt_depts is table of dept_emp_rel%rowtype;
  function add_seq return tt_depts pipelined;
end dept_emp;
/

create or replace package body dept_emp as
  function add_seq return tt_depts pipelined is
    l_prev_deptno dept_emp_rel.deptno%type := 0;
    l_seqno integer;
  begin
    for rec in (select * from dept_emp_rel order by deptno, rn) loop
      if rec.deptno > l_prev_deptno then
        l_prev_deptno := rec.deptno;
        l_seqno := dept_seq.nextval;
      end if;
      rec.deptno := l_seqno;
      if rec.rn is null then
        rec.empno := null;
      else
        rec.empno := emp_seq.nextval;
      end if;
      pipe row(rec);
    end loop;
  end add_seq;
end dept_emp;
/
select * from table(dept_emp.add_seq);
DEPTNO DNAME LOC RN EMPNO ENAME JOB HIREDATE SAL COMM
1 ACCOUNTING NEW YORK 1 1 CLARK MANAGER 1981-06-09 2450
1 ACCOUNTING NEW YORK 2 2 MILLER CLERK 1982-01-23 1300
1 ACCOUNTING NEW YORK 3 3 KING PRESIDENT 1981-11-17 5000
2 RESEARCH DALLAS 1 4 SMITH CLERK 1980-12-17 800
2 RESEARCH DALLAS 2 5 FORD ANALYST 1981-12-03 3000
2 RESEARCH DALLAS 3 6 ADAMS CLERK 1987-05-23 1100
2 RESEARCH DALLAS 4 7 SCOTT ANALYST 1987-04-19 3000
2 RESEARCH DALLAS 5 8 JONES MANAGER 1981-04-02 2975
3 SALES CHICAGO 1 9 ALLEN SALESMAN 1981-02-20 1600 300
3 SALES CHICAGO 2 10 JAMES CLERK 1981-12-03 950
3 SALES CHICAGO 3 11 TURNER SALESMAN 1981-09-08 1500 0
3 SALES CHICAGO 4 12 BLAKE MANAGER 1981-05-01 2850
3 SALES CHICAGO 5 13 MARTIN SALESMAN 1981-09-28 1250 1400
3 SALES CHICAGO 6 14 WARD SALESMAN 1981-02-22 1250 500
4 OPERATIONS BOSTON

And now my INSERT ALL statement works like a charm:

insert all
when rn = 1 or rn is null then
  into dept(deptno, dname, loc)
     values(deptno, dname, loc)
when rn is not null then
  into emp(EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)
    values(EMPNO, ENAME, JOB, HIREDATE, SAL, COMM, DEPTNO)
select * from table(dept_emp.add_seq);

Conclusion

When foreign keys reference primary keys that are themselves generated from sequences, loading the parent and child tables together is a great idea – if we can increment those sequences correctly. A simple pipelined function gives us the control we need with few lines of code and little overhead.

Advertisements

5 thoughts on “Multi-Table Inserts with Sequences

  1. Stu, I think dept_emp_rel needs to be ordered by dept in add_seq, otherwise the sequence could advance unnecessarily if say dept_no goes 1, 2, 1?

  2. Hi paulzip,

    If you don’t mind, it’s “Stew” not “Stu” ;)

    With FOR ORDINALITY, I have always assumed that the ascending order would be respected in the output. Do you know otherwise?

    If that assumption is unfounded, then you are right about the ORDER BY.

    Best regards, Stew

    • Stew, oops. No offence intended, I have three friends all called Stu(art), so it’s just habit!

      You are left joining (although with chained XMLTable it’s more a left outer apply join), and with any join scenario Oracle gives no guarantee on the order of returned rows. Sets are order agnostic and as you know Oracle and DBs work on sets. You might be OK and the “For Ordinality” might come back in the correct order, but it isn’t guaranteed.

      It’s not unlike the problem you’re trying to solve with insert all with sequence (IAWS) situation. I’ve seen production code based on (IAWS) which assumes order, works for ages relying on implied order, then all of a sudden “…parent key not found” strikes.

      • p.s. Great novel approach to the IAWS problem though, it’s something I’ll add to my arsenal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s