Saturday, January 26, 2008

Sequence within parent

The sequence within parent column is the column in a detail table that, together with the foreign key column to the master table, constitutes a unique key of the detail table. And sometimes even the primary key if you are not into surrogate keys. A small example of a sequence within parent:

rwijk@ORA11G> create table orders
2 ( id number(10) not null primary key
3 , customer_id number(10) not null
4 , order_date date not null
5 )
6 /

Tabel is aangemaakt.

rwijk@ORA11G> create table order_lines
2 ( order_id number(10) not null references orders(id)
3 , sequence_within_parent number(6) not null
4 , product_id number(10) not null
5 , quantity number(6) not null
6 , price_per_piece number(10,2) not null
7 , primary key (order_id,sequence_within_parent)
8 )
9 /

Tabel is aangemaakt.


The purpose is to let the sequence within parent column start with the value 1 for each order_id and increase it by 1 for each subsequent order_line within the same order. In the days of Designer/2000 you could mark a column with a so-called "Autogen Type" as a sequence within parent, and the Oracle*Forms Generator of Designer would conveniently generate a Pre-Insert trigger at the block level containing a call to a function containing this piece of code:

DECLARE
CURSOR C IS
SELECT nvl(max(SEQUENCE_WITHIN_PARENT),0) + 1
FROM ORDER_LINES OLE
WHERE OLE.ORDER_ID = P_ORDER_ID;
BEGIN
OPEN C;
FETCH C INTO P_SEQUENCE_WITHIN_PARENT;
CLOSE C;
END;


But the problem with this code is that it won't function in a multi user environment, because if this code is executed in two different sessions for the same order_id, both sessions would get the same sequence_within_parent. One would succeed and the other would fail because of the unique index on (order_id,sequence_within_parent). Also, and probably more important, the sequence within parent makes it necessary to execute a select statement to be able to insert a new row. Which harms performance.

So why use the sequence within parent? Are you really going to query the third order_line for order_id 1? I doubt it. In my experience, the sequence within parent is often lightheartedly introduced by the designer "just to be able to have an easy unique identifier". The only case I can see where this column is useful, is when it is important to be able to quickly search for the n-th order_line, and when the total number of detail records within one master record can be a big number. And as said, this case is very rare.

Suppose the tables above are filled like this:

rwijk@ORA11G> insert into orders
2 select 1, 1234, date '2008-01-01' from dual union all
3 select 2, 5678, date '2008-01-20' from dual union all
4 select 3, 1234, date '2008-02-01' from dual
5 /

3 rijen zijn aangemaakt.

rwijk@ORA11G> insert into order_lines
2 select 1, 1, 987, 10, 25 from dual union all
3 select 1, 2, 876, 1, 500 from dual union all
4 select 1, 3, 765, 50, 2 from dual union all
5 select 2, 1, 654, 1, 750 from dual union all
6 select 3, 1, 99, 1, 22.95 from dual union all
7 select 3, 2, 98, 1, 67.99 from dual union all
8 select 3, 3, 97, 5, 9.99 from dual union all
9 select 3, 4, 96, 1, 19.99 from dual union all
10 select 3, 5, 95, 10, 24.95 from dual
11 /

9 rijen zijn aangemaakt.


An insert, placed in an api of course, would be something like this:

rwijk@ORA11G> insert into order_lines
2 ( order_id
3 , sequence_within_parent
4 , product_id
5 , quantity
6 , price_per_piece
7 )
8 values
9 ( 3
10 , (select nvl(max(sequence_within_parent),0)+1 from order_lines where order_id=3)
11 , 90
12 , 1
13 , 4.99
14 )
15 /

1 rij is aangemaakt.

rwijk@ORA11G> select *
2 from order_lines
3 where order_id = 3
4 /

ORDER_ID SEQUENCE_WITHIN_PARENT PRODUCT_ID QUANTITY PRICE_PER_PIECE
---------- ---------------------- ---------- ---------- ---------------
3 1 99 1 22,95
3 2 98 1 67,99
3 3 97 5 9,99
3 4 96 1 19,99
3 5 95 10 24,95
3 6 90 1 4,99

6 rijen zijn geselecteerd.


The alternative is to use a sequence based id column and have a unique key - the unique identifier in ERD terms - on (order_id,product_id). Like this:

rwijk@ORA11G> drop table order_lines
2 /

Tabel is verwijderd.

rwijk@ORA11G> create table order_lines
2 ( id number(10) not null primary key
3 , order_id number(10) not null references orders(id)
4 , product_id number(10) not null
5 , quantity number(6) not null
6 , price_per_piece number(10,2) not null
7 , unique (order_id,product_id)
8 )
9 /

Tabel is aangemaakt.

rwijk@ORA11G> insert into order_lines
2 select 1, 1, 987, 10, 25 from dual union all
3 select 2, 1, 876, 1, 500 from dual union all
4 select 3, 1, 765, 50, 2 from dual union all
5 select 4, 2, 654, 1, 750 from dual union all
6 select 5, 3, 99, 1, 22.95 from dual union all
7 select 6, 3, 98, 1, 67.99 from dual union all
8 select 7, 3, 97, 5, 9.99 from dual union all
9 select 8, 3, 96, 1, 19.99 from dual union all
10 select 9, 3, 95, 10, 24.95 from dual
11 /

9 rijen zijn aangemaakt.

rwijk@ORA11G> create sequence order_lines_seq start with 10 increment by 1
2 /

Reeks is aangemaakt.


The insert now becomes much easier:

rwijk@ORA11G> insert into order_lines
2 ( id
3 , order_id
4 , product_id
5 , quantity
6 , price_per_piece
7 )
8 values
9 ( order_lines_seq.nextval
10 , 3
11 , 90
12 , 1
13 , 4.99
14 )
15 /

1 rij is aangemaakt.


It is simple, scalable and always works in a multi-user environment.
And in case the end user, REALLY likes to see a "sequence within parent" column in his gui, then you just define a view like this:

rwijk@ORA11G> create view v_order_lines
2 as
3 select order_id
4 , row_number() over (partition by order_id order by id) sequence_within_parent
5 , product_id
6 , quantity
7 , price_per_piece
8 from order_lines
9 /

View is aangemaakt.

rwijk@ORA11G> select *
2 from v_order_lines
3 where order_id = 3
4 /

ORDER_ID SEQUENCE_WITHIN_PARENT PRODUCT_ID QUANTITY PRICE_PER_PIECE
---------- ---------------------- ---------- ---------- ---------------
3 1 99 1 22,95
3 2 98 1 67,99
3 3 97 5 9,99
3 4 96 1 19,99
3 5 95 10 24,95
3 6 90 1 4,99

6 rijen zijn geselecteerd.

2 comments:

  1. Last year on the seminar by J.Lewis it was interesting to hear, oracle itself is using this handy technique in the data dictionary. For example sys.tabpartv$ (on which dba_tab_partitions is built) contains following snippet

    select
    ...
    row_number() over (partition by bo# order by part#),
    ...
    from tabpart$

    Best regards

    ReplyDelete
  2. Thanks very interesting

    ReplyDelete