random 6 digit number

[uparna]

Here is the situation i am in:-

I have to generate a random 6 digit number. Sounds simple.

SELECT ROUND(DBMS_RANDOM.VALUE(100000,999999)) NUM FROM DUAL;

Ah! here are the couple of catches:-

1) This number should not be a previously generated number ( stored in a difft table, called table X)

2) This number should start with "5" for canadian companies and can start with any number for others.

Here is the pseudo code that i can think of now:-

BEGIN

	check country (A1) 
	
	If [COLOR=green]Canada 
	


		SELECT ROUND(DBMS_RANDOM.VALUE(500000,599999)) INTO vHOLD FROM DUAL; 
		
		check vHold  (A2)
	

	
		if already used , generate again , repeat check A2
		
		else , assign vHold and get out.[/color]
	

	else [COLOR=blue](not canada)
	


		SELECT ROUND(DBMS_RANDOM.VALUE(100000,999999)) INTO vHOLD FROM DUAL; 
		
		check vHold  (B1)
		
		if first digit is 5, add 1 to it
		
		else
		

		if already used , generate again , repeat check B1
		

		else , assign vHold and get out.[/color]


	end if
	
EXCEPTION HANDLER
END

This looks more like c++ thinking than Oracle thinking. [COLOR=red yellow]Any ideas to help me think alternatively?
[/color]

The current process is an ancient program -- it uses a table (lets call this table MASTER) that has 1 column and 700,000 rows ( all having various combinations of 6 digit numbers) and searches for another number that hasnt been already used , like i mentioned above.

Even though there is an index on this column, when a search is conducted on this table with a series of NOT EXISTS and NOT LIKEs , it uses a lot of CPU and takes a couple of seconds , which is not good when this has to run repeatedly (more than 1000 times every hour or so). The table X has millions of rows.

Regards,
S. Jayaram Uparna .

 

[Dagon]

Why do you need to use a random number rather than an ascending sequence ?

To improve it, you could do generate a whole batch of numbers into a temporary table, then left join the temporary table to the MASTER table to find which ones have already been used. This would be more efficient than running a separate check for each number found.