Re: User Sequence Numbers

On Mon, 03 Feb 2003 09:42:13 +0100, Hermann Angstl <hal_at_camline.com> wrote:

>Karsten Farrell wrote:
>
>> insert into child_table (parent_name,child_nr,...)
>> select parent_name,
>> (
>> select decode(child_nr,null,1,max(child_nr)+1)
>> from child_table
>> where child_table.parent_name(+) = parent_table.parent_name
>> )
>> from parent_table
>
>Maybe - but remember: i am using multiple sessions. And i fear that oracle makes no
>atomic transaction. For example the *substatements* might be executed in *parallel*
>in the different sessions.
>
>Especially the "select decode(child_nr,null,1,max(child_nr)+1)" is critical.
>
>Imagine the following execution sequence:
>assume child_nr = 5
>
>Session 1: do subselect: select decode(child_nr,null,1,max(child_nr)+1) -> 6
>Session 2: do subselect: select decode(child_nr,null,1,max(child_nr)+1) -> also
>6
>Session 1: insert into child_table (parent_name,child_nr,...) ... select
>parent_name, (... result from above ...)...
>Session 2: insert into child_table (parent_name,child_nr,...) ... select
>parent_name, (... result from above ...)...
>
>So the vital question is: Does Oracle execute your statement ATOMIC ?
>
>cu,
>hal
>

Using MAX for a user-coded sequence can lead to poor performance - depending on the number of inserts and existing childs - and is not necessary because of the existance of sequences. So it seems like a non-professional approach of a non existing problem. Oracle solved this problem for you and manu others.

Even thoug performance is not an issue, it is bad practice to use MAX for this.

As mentioned before, you really CAN use a sequence for this. Increment the sequence in a trigger and locking is not an issue in standard behaviour. If you use a sequence you have unique ID's for every record, thus also for every child-record of any parent. Ok, there can be gaps in the sequence of childs per parent but they are unique and you can use it for sorting (order by). You did not indicate that gaps in the sequence are a problem, on the other hand you also get gaps when deleting a child that isnt the last one from its parent.

So, what's your problem?

Groeten,

Hans van Dam. Received on Tue Feb 04 2003 - 02:24:54 CST