Getting free ID from DB, how to do it safely or performance wise

[Former Member]

Hi,

--- short version ---

I need to get free ID from table. So if key is a 4 digit number, some ID's are already taken (not continuous so 4, 9, 11, 12) I need to be able to select free IDs so 1, 2, 3, 5, 6, 7, 8, 10, 13+.

--- /short version ---

--- full version ---

Scenario:

There exists a table with key field ID type NUMC length 4 (and some other columns) and a transaction to add/remove entries from the table.

Client wants to import some legacy data there first forcing specific ID numbers.

After data import, the system should automatically propose "FREE NUMBER" in DB.

E.g.

Client's IDs:

0001

0005

0009

0010

From now on, system should automatically propose all the free IDs that are left so, in this case FREE NUMBER = 2, then 3, then 4, then 6, etc etc, up to 9999 excluding 0009 and 0010. Data inserting reports are to be executed by multiple users concurrently, so two users can work on the table "at the same time". Also deletion of records thus freeing the numbers should be possible.

Is it possible to solve it in an elegant way ?

I thought of the following scenario(low performance I'd say, not yet sure whether safe):

1. disable table buffering

2. lock table before selecting free ID (ENQUEUE_EZ_LOCK)

2.a.. select all entries from table

2.b.. find free ID

2.c.. insert entry

3. unlock table (DEQUEUE_EZ_LOCK).

Any suggestions appreciated.

Cheers,

Bart

Edited by: Thomas Zloch on Jul 13, 2011 11:53 AM

[Former Member]

May be you could create a number range object for the same . And in your table maintenance program , when you read the next id from the number range object , you could validate if the id already exists in the application table , if so read one more from the number range object till you find one(in a loop). Since its a key field , I don't expect it should have any performance issue. But the impact depends on your actual application. Sorry if I have not understood your problem right.

Hope this helps.

[Former Member]

Hi,

You were close, but the problem is that removal of entries is possible thus we have to "go back" and find the gap with older ID.

Cheers,

Bart

[Former Member]

hi,

you need to break your code into two parts.

1)Find out unused IDs.

2)Create new entries for them

try this:

You can select all Ids from table.

add these Ids to a range internal table. [Range_used]

define a range for the available options for ID values. [Eg 1-1000]. [range_total]

delete range_total where id not in range_used.

check on the syntaxes