Re: How to get available IDs?

From: Stephan Born <stephan.born_at_beusen.de>
Date: Wed, 17 Jan 2001 16:27:55 +0100
Message-ID: <3A65B9FB.41D1C36C@beusen.de>

Thorsten Kettner wrote:
>
> Hi, I think this is a beginner's question: I want to get all available
> employee numbers by sql to put new employees into the system. If emp_no
> is NUMBER(3) then this is how I do it:
>
> SELECT rownum - 1 FROM very_big_table WHERE rownum <= 1000
> MINUS
> SELECT emp_no FROM emp
>
> The problem is that I need that very_big_table to do the trick. If I
> want to to the same for article numbers NUMBER(6) for example, that
> very_big_table had to be even bigger!
>
> Is there another simple way I just haven't thought of? I've thought of
> MIN and MAX from emp, but they don't help as 0 and 999 might be used,
> whereas the other numbers aren't. I have thought about sequences also,
> but a sequence doesn't know about which numbers are still in use, so it
> doesn't help either. Any ideas?

I have a solution which will not give you all unused ids but gives you the
first unused id in a range

Do the following:

---

• 1. create a test-table and fill it with data

create table range_test
(

        id number unique not null
);

insert into range_test
values (1);

insert into range_test
values (4);

insert into range_test
values (6);

insert into range_test
values (7);

insert into range_test
values (8);

insert into range_test
values (11);

insert into range_test
values (12);

insert into range_test
values (14);

insert into range_test
values (15);

insert into range_test
values (17);

insert into range_test
values (18);

insert into range_test
values (19);

commit;

• 2. you see there are gaps in the ids

select id
from range_test
order by id;

• 3. Now create the function

create or replace function first_free_id (min_id_	in number default 1,
					  max_id_	in number default 999999999) return number as
	cursor c_range_of_used_ids (min_id in number, max_id in number) is
		select min(id) id, 'x'
		from range_test
		where id between min_id and max_id
		group by round(id/2)
		having count(/* distinct */id) = 1
		union
		select min(id), 'xx'
		from range_test
		where id between min_id and max_id
		group by round((id+1)/2)
		having count(/* distinct */ (id+1)) = 1;
--		
	first_range_start	number;
	first_range_end		number;
	dummy				varchar2(2);
begin
	open c_range_of_used_ids (min_id_, max_id_);
--	
	fetch c_range_of_used_ids into first_range_start, dummy;
--	
	-- Starts the first range with used IDs beyond 1?
	if first_range_start > min_id_ then
		close c_range_of_used_ids;
		return min_id_;
	end if;
--	
	fetch c_range_of_used_ids into first_range_end, dummy;
--	
	if first_range_end + 1 > max_id_ then
		-- there are no free ids in this range
		close c_range_of_used_ids;
		return 0;
	end if;
--	
	close c_range_of_used_ids;
--	
	return first_range_end + 1;
exception
	when others then
		close c_range_of_used_ids;
		raise;

end first_free_id;

/

• 4.
• Now, let this PL/SQL-Block run several times
• you will see, it fills the gaps in ID
• until it reaches the upper limit

declare

        new_id number;
begin

	new_id := first_free_id(1, 20);
	insert into range_test (id)
	values (new_id);
	dbms_output.put_line (new_id);

end;

/

---

I do not know about the performance...for big tables it can be slow... try it...

the select-statement in the function is used by us to get the range of used IDs

Regard, Stephan Received on Wed Jan 17 2001 - 09:27:55 CST