This content has been marked as final. Show 59 replies
I do not understand what your function actually returns. What is the datatype?
You need to understand that you will not be able to get a gap-free, always-increasing result with a SEQUENCE. It's not going to happen, especially if you have a RAC configuration and/or specify a CACHE on the sequence. So don't try - you are starting with the wrong building blocks.
If you must have gap-free, always-ascending values, then I suggest creating a table and generating a pool of values into it. Then your 'function' (but it is not a function, it should be a procedure, because it would cause updates/deletes) can do soemthing like this:
If you need to issue them in sets of 1000, then why generate the individual values (and why try to isolate 1000 individual values)? Just record the starting values as 1000, 2000, etc. and calculate the rest from there.
DELETE FROM KEY_TABLE WHERE KEY_VALUE = (SELECT MIN(KEY_VALUE) FROM KEY_TABLE ) RETURNING KEY_VALUE INTO return_value;
But this is not a sequence, it will not perform as well as a sequence, and it will be a bottleneck because there is only one correct value that everyone will want.
I am trying to be as clear as possible in my original posting in what I am really trying to achieve. I guess I am not going to get a proper answer here...I talked to my client, asked him to look at the thread him self as well. He read my description and replied to me that, thats exactly what he wants, becasue in his application, multiple processes run and they all want to get sequence numbers in chunks of any quantity, and when one process is getting the numbers from the sequence, and if at the same time another process comes, and try to grab numbers from the sequence, it should wait till the first process is finished and then grab the numbers after the first one is gone...the numbers are used as PK in the tables related to those sequences, meaning, table name test will only get sequence numbers as it PK from the sequence name test. The client do not want any holes in the sequence numbers and want the numbers to be consecutive for every process which goes and grabs the numbers.....every process has it own name and gets populated in the table....example
ID | Process | Proc. Start Time
1 proc1 10:00:00
4 proc2 10:00:00
7 proc3 10:00:00
I hope I have clearified my self as much as I can....I am thinking on the grounds of having a table with a lock flag in it ac a column, and every time a process runs, it looks at the table and if it see that the flag is running, it waits till the flag turns stop, and then start running....measning...I want to run all teh consecutive running processes in a que....Hope I am more clearer now..a response will be appreciate with a solution.
mkmunir wrote:Then the client wants a very slow application that supports a maximum of one process at a time.
The client do not want any holes in the sequence numbers and want the numbers to be consecutive
Access or using plain text files would be more suitable since it would be a waste of software license dollars to use Oracle.
Ok, here goes...
Now retrieve blocks of numbers as follows:
create table my_sequences (seq_name varchar2(10) not null primary key ,current_value number not null); insert into my_sequences values('seq1',0); insert into my_sequences values('seq2',0); -- etc... create type num_t as table of number; / create or replace function seq_fun(p_seq_name in varchar2, p_block_size in number) return num_t PIPELINED as pragma autonomous_transaction; pl_current_value number; begin -- select current_value into pl_current_value from my_sequences where seq_name = p_seq_name for update of current_value; -- update my_sequences set current_value = current_value + p_block_size where seq_name = p_seq_name; -- commit; -- for i in pl_current_value..pl_current_value+p_block_size - 1 loop pipe row(i); end loop; -- end; /
Of course the question is whether you really need this to be a query that returns the sequence numbers each as one row.
select column_value as seqnum from table(seq_fun('seq1',100));
A more simple solution would be to have a stored procedure just succesfully complete, indicating the block was succesfully 'grabbed'.
What you've yet to explain is why you need to serialize these transactions. That is what you are attempting to do. Hold process 2 from starting until process 1 has completed .... is it not?
The sequence question is ancillary to that. If you are using these sequence values as PK values as you say then your processes should just run and grab sequence values as they require them, there's NO reason to introduce a scalability inhibitor as you are asking to do.
Unless there's some legitimate reason you need to serialize the process, but there's nothing in your post up until now which suggests this.
mkmunir wrote:Then sequences are NOT the appropriate tool to use.
The client do not want any holes in the sequence numbers and want the numbers to be consecutive for every process which goes and grabs the numbers.....every process has it own name and gets populated in the table....example
From the Oracle Documentation:
If your application can never lose sequence numbers, then you cannot use Oracle sequences, and you may choose to store sequence numbers in database tables. Be careful when implementing sequence generators using database >tables. Even in a single instance configuration, for a high rate of sequence values generation, a performance overhead is associated with the cost of locking the row that stores the sequence value.
The client do not want any holes in the sequence numbers and want the numbers to be consecutive for every process which goes and grabs the numbers....And what makes your client want that?
Your client (and you) might want to read:
and there's more....just do a search yourself.
Your client seriously needs to rethink why holes in sequence numbers shouldn't be allowed.
mkmunir wrote:As you've previously pointed your client to this thread, hopefully they will read this.
The client do not want any holes in the sequence numbers and want the numbers >to be consecutive for every process which goes and grabs the numbers.....every >process has it own name and gets populated in the table....example
A "Gapless" number requirement is really nothing more than a requirement for slow running, non-scalable systems.
This will not necessarily be gap-free - since there is an autonomous transaction there, which commits the 'burning' of the key value. If your transaction gets an error after this, before it is able to insert them into wherever they are to live from then on, then you will have a gap. The error handling for the transaction can not put back the values that you retrieved in the autonomous transaction.
And, if that is OK, that there is even a remote possibility of having a gap... you should see how many of the other 'requirements' can be relaxed or removed.