Becoming Unhinged with INSERT ALL and SEQUENCE.NEXTVAL

However well travelled you may be, after a while, you’re likely to conclude that there’s no taste like home.
Obviously, the taste in question will depend entirely on where it is you call home.
Being the native of a country that’s only discovered culinary excellence in the last 20 years or so, my tastes of home may be somewhat surprising to a non-native of the UK.
Chip shop chips, that goes without saying…although Belgians may take issue with any attempt to claim “Frites” as being a British dish.
Then there are the other “British” staples – a nice Lamb Kourma, or possibly even Sweet and Sour Chicken Hong Kong style.
It’s probably much simpler if your a Pole. To you home may well be Pierogi, the traditional Polish dumplings, usually containing a savoury filling but which can also hold something sweet.

Whatever it is, that taste of home is something familiar and reliable, rather like the ever-reliable sequence.NEXTVAL in Oracle…

Test tables – yet another contrived example

We have a table containing details of dishes from around the world :

create table dishes_stg
(
    dish varchar2(100),
    country varchar2(50),
    sweet_flag varchar2(1),
    savoury_flag varchar2(1),
    description varchar2(4000)
)
/

insert into dishes_stg( dish, country, sweet_flag, savoury_flag, description)
values( 'CHIPS', 'UK', 'N', 'Y', 'Like Belgian frites but served with Malt Vinegar rather than Mayonnaise')
/

insert into dishes_stg( dish, country, sweet_flag, savoury_flag, description)
values( 'PIEROGGI', 'POLAND', 'Y', 'Y', 'Dumplings with a sweet or savoury filling')
/

commit;

We’re going to use the records in this staging table to populate two further tables, a summary table and a detail table, which look like this :

create table dish_summaries
(
    dish varchar2(100) constraint ds_pk primary key,
    country varchar2(50)
)
/

create table dish_details
(
    id number constraint dd_id_pk primary key,
    dish varchar2(100) constraint dd_ds_fk references dish_summaries(dish),
    flavour varchar2(10),
    description varchar2(4000)
)
/

create sequence dd_id_seq
/

NOTE – as you can probably tell from the code, we’re running on 11g here. In 12c, you’d setup the synthetic key and the sequence in the create table statement.

You would also then avoid the trap that I’m about to fall into…

As you can see from this, the detail table may hold up to two records for each summary record.
The most efficient way to populate these tables would seem to be to use a multi-table insert…

Breaking SEQUENCE.NEXTVAL

Running this seemingly innocent statement …

insert all
when 1=1 then
    into dish_summaries( dish, country)
    values( dish, country)
when sweet_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( dd_id_seq.nextval, dish, 'SWEET', description)
when savoury_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( dd_id_seq.nextval, dish, 'SAVOURY', description)
select dish, country, sweet_flag, savoury_flag, description
from dishes_stg
/

…we get a nasty surprise…

insert all
*
ERROR at line 1:
ORA-00001: unique constraint (MIKE.SYS_C0011279) violated

That’s odd, it’s not like NEXTVAL returns the same value in successive calls, is it ?

select dd_id_seq.currval
from dual
/

   CURRVAL
----------
	  2

SQL>   

Hmmm. Let’s try that again only this time, we’ll disable the primary key on the DISH_DETAILS table…

alter table dish_details drop constraint dd_id_pk;

Table altered.

insert all
when 1=1 then
    into dish_summaries( dish, country)
    values( dish, country)
when sweet_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( dd_id_seq.nextval, dish, 'SWEET', description)
when savoury_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( dd_id_seq.nextval, dish, 'SAVOURY', description)
select dish, country, sweet_flag, savoury_flag, description
from dishes_stg
/

select id, dish, flavour
from dish_details
order by id
/

 ID DISH			   FLAVOUR
--- ------------------------------ ----------
  1 CHIPS			   SAVOURY
  2 PIEROGGI			   SAVOURY
  2 PIEROGGI			   SWEET

Despite invoking DD_ID_SEQ.NEXTVAL twice, it appears that it’s only executed once per statement iteration with, as they say, hilarious results.
So, what’s gone wrong ? Is this some fiendish bug in Oracle ? Well, if we take a moment to check the documentation, we can see that…

“You cannot specify a sequence in any part of a multitable insert statement. A multitable insert is considered a single SQL statement. Therefore, the first reference to NEXTVAL generates the next number, and all subsequent references in the statement return the same number.”

By this point, you may well be feeling a bit peckish so a solution to this particular issue needs to present itself.
What then, are the alternatives…

Sequence increment value

Let’s re-set everything and try a different approach :

truncate table dish_details
/

alter table dish_details disable constraint dd_ds_fk
/

truncate table dish_summaries
/

alter table dish_details enable constraint dd_ds_fk
/

We know that the maximum number of records being inserted into DISH_DETAILS will be two. So, first, we change the sequence to increment by two each time it’s called :

alter sequence dd_id_seq increment by 2
/

We can then amend our insert statement as follows :

insert all
when 1=1 then
    into dish_summaries( dish, country)
    values( dish, country)
when sweet_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( dd_id_seq.nextval, dish, 'SWEET', description)
when savoury_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( dd_id_seq.nextval + 1, dish, 'SAVOURY', description)
select dish, country, sweet_flag, savoury_flag, description
from dishes_stg
/

This now executes without error and the results are what we’re after….

select id, dish, flavour
from dish_details
order by id
/

 ID DISH			   FLAVOUR
--- ------------------------------ ----------
  3 CHIPS			   SAVOURY
  4 PIEROGGI			   SWEET
  5 PIEROGGI			   SAVOURY

Hang on though, the sequence value is now less than the maximum value in the ID column, won’t we hit the same problem in the next run ?
Let’s check…

select dd_id_seq.currval     
from dual;

   CURRVAL
----------
	 4

select dd_id_seq.nextval  
from dual;

   NEXTVAL
----------
	 6

SQL> 

The next value will be one more than the current maximum ID value in the table because we’ve set the sequence to increment by two.

Whilst this solution meets our current needs, it’s worth noting that it has the potential to cause problems further down the line.
For example, if had an addtional flavour, say SOUR for the sake of argument, we’d then need to insert up to three records into the DISH_DETAILS table each time the statement ran.
In such circumstances, we’d have to remember to adjust the sequence as well.

OK then, what about…

The DML trigger

With the assignment of the sequence value in a trigger like this…

create or replace trigger dd_bir_trg
    before insert on dish_details
    for each row
begin
    :new.id := dd_id_seq.nextval;
end;
/

…our statement can now be modified to…

insert all
when 1=1 then
    into dish_summaries( dish, country)
    values( dish, country)
when sweet_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( null, dish, 'SWEET', description)
when savoury_flag = 'Y' then
    into dish_details( id, dish, flavour, description)
    values( null, dish, 'SAVOURY', description)
select dish, country, sweet_flag, savoury_flag, description
from dishes_stg
/

…and the result is once again, what we’re after…

select id, dish, flavour
from dish_details
order by id
/

	ID DISH 			  FLAVOUR
---------- ------------------------------ ----------
	 1 CHIPS			  SAVOURY
	 2 PIEROGGI			  SWEET
	 3 PIEROGGI			  SAVOURY

SQL> 

Whilst this solution is a bit more robust in the sense that additional records being inserted will not require code changes other than to the insert statement itself, there is a bit of a catch.
If we’re loading a large number of rows, the firing of the trigger for every row is likely to slow things down somewhat.

Maybe we should just do away with the multi-table insert altogether…

Multiple Insert Statements with UNPIVOT

Ostensibly, this is the simplest alternative. After all, inserting to the DISH_SUMMARIES table is rather straightforward as you need to create one row for each row in the staging table.

The DISH_DETAILS table requires a bit more thought. We want one row for each flavour type.
Potentially then, we’ll need to generate multiple rows for a single row in the staging table.
In order to avoid becoming somewhat unhinged at this point, let’s just UNPIVOT instead…

with unpiv_flavours as
(
select dish, country, 
    sweet_flag, savoury_flag, description
from dishes_stg
)
select dish, country, flavour
from unpiv_flavours
unpivot (flavour_flag for flavour in  (sweet_flag as 'SWEET', savoury_flag as 'SAVOURY') )
where flavour_flag = 'Y'
/

DISH		     COUNTRY		  FLAVOUR
-------------------- -------------------- --------------------
CHIPS		     UK 		  SAVOURY
PIEROGGI	     POLAND		  SWEET
PIEROGGI	     POLAND		  SAVOURY

So, our two statement solution looks like this :

insert into dish_summaries( dish, country)
select dish, country
from dishes_stg
/

insert into dish_details(id, dish, flavour, description)
    with unpiv_flavours as
    (
        select dish, sweet_flag, savoury_flag, description
        from dishes_stg
    )
    select dd_id_seq.nextval, dish, flavour, description
    from unpiv_flavours
    unpivot (flavour_flag for flavour in  (sweet_flag as 'SWEET', savoury_flag as 'SAVOURY') )
    where flavour_flag = 'Y'
/

After all that, I now have a strange craving for beans on toast.

Advertisements

3 thoughts on “Becoming Unhinged with INSERT ALL and SEQUENCE.NEXTVAL

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