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.
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
ReplyDeleteselect
...
row_number() over (partition by bo# order by part#),
...
from tabpart$
Best regards
Thanks very interesting
ReplyDelete