CODEWITHSUNDEEP
sqloracle-databasesequence
FtDRbwLXw6
FtDRbwLXw6

Reputation: 28889

How do I create sequential numbers across records in Oracle?

I have two tables, foo and bar:

foo
,----------------.
|id | name | ... |
|---+------+-----|
| 1 | abc  | ... |
| 2 | xyz  | ... |
| ...            |
`----------------'

bar
,-------------------------.
| id | foo_id | num | ... |
|----+--------+-----+-----|
| 1  | 1      | 1   | ... |
| 2  | 1      | 2   | ... |
| 3  | 1      | 3   | ... |
| 4  | 2      | 1   | ... |
| 5  | 2      | 2   | ... |
| ...                     |
`-------------------------'

For each foo record, there are many bar records, each with a slot number (num), which should be a value from 1 - N for each foo_id. Thus, there's a unique key on (foo_id, num). These slot numbers are meaningful, and must be sequential.

Because of that last constraint, I can't use Oracle's native sequences (as they aren't guaranteed to be sequential, and don't seem to be meant for values that are meaningful). The number of foo records is also extermely large, and having a sequence for each foo_id seems highly impractical anyway.

So, my question is - what approaches exist for creating these numbers on insertion that would hold up under high concurrency? Is my only option to lock and select MAX(num) + 1, and release the lock on commit?

Upvotes: 0

Views: 143

Answers (2)

vav
vav

Reputation: 4694

you can use a 2 step aproach:

  1. you can put your records into database concurrenly using sequence

  2. run a script that will populate num out of sequence numbers:

    --q1
insert into bar (id, foo_id, seq_num) values (:id, :foo, someseq.next_val)

--q2
update bar b1 set
num = (select 1+count(*) from bar b2 
where b2.foo_id = b1._foo_id 
and b2.seq_num < b1.seq_num)
where num is null

Or you could have bar with gaps, but then have a view bar_vw that would be correcting it on a fly:

     create view bar_vw as
        select id, foo_id, 
        row_number() over (partition by foo_id, order by seq_num) as num 
     from bar

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231661

If there is a way to architect the system such that only one session at a time is inserting bar rows for any particular foo row, that would be ideal since that would eliminate actual contention (I assume you'd still want to code to handle it just in case).

To manage concurrency, you'd almost certainly need to lock the parent foo row (select for update) and then do the max(num) + 1 from the bar rows with that foo_id. I suppose you could potentially omit the step of locking the foo record and create a materialized view that does a fast refresh on commit to validate that the num values are sequential but it seems highly unlikely that this would be faster and it seems highly likely that it would make the code more complex since you're now catching exceptions at commit time rather than simply blocking until you can move forward.

Upvotes: 0

Related Questions