This discussion is archived
1 2 3 4 Previous Next 59 Replies Latest reply: Sep 24, 2010 5:29 AM by 635471 Go to original post RSS
  • 15. Re: Sequence Issue
    731280 Explorer
    Currently Being Moderated
    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:
    DELETE FROM KEY_TABLE WHERE KEY_VALUE =
         (SELECT MIN(KEY_VALUE) FROM KEY_TABLE ) 
    RETURNING KEY_VALUE INTO return_value;
    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.
    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.
  • 16. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    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

    Table: Test
    --------------------------------------------------------------
    ID | Process | Proc. Start Time
    -------------------------------------------------------------
    1 proc1 10:00:00
    2 proc1
    3 proc1
    4 proc2 10:00:00
    5 proc2
    7 proc3 10:00:00
    8 proc3

    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.
  • 17. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    mkmunir wrote:

    The client do not want any holes in the sequence numbers and want the numbers to be consecutive
    Then the client wants a very slow application that supports a maximum of one process at a time.

    Access or using plain text files would be more suitable since it would be a waste of software license dollars to use Oracle.
  • 18. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    Ok, here goes...
    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;
    /
    Now retrieve blocks of numbers as follows:
    select column_value as seqnum from table(seq_fun('seq1',100));
    Of course the question is whether you really need this to be a query that returns the sequence numbers each as one row.
    A more simple solution would be to have a stored procedure just succesfully complete, indicating the block was succesfully 'grabbed'.
  • 19. Re: Sequence Issue
    Tubby Guru
    Currently Being Moderated
    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.
  • 20. Re: Sequence Issue
    584412 Journeyer
    Currently Being Moderated
    mkmunir wrote:
    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
    Then sequences are NOT the appropriate tool to use.
    From the Oracle Documentation:
    Documentation:Caution:
    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.
  • 21. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    I will try this solution, and it it works, thn you are the MAN...you ge the maximum points for this answer if the solution works...Thanks anyways for your response.
  • 22. Re: Sequence Issue
    Hoek Guru
    Currently Being Moderated
    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:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4343369880986
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1508205334476
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1133611000346070688
    and there's more....just do a search yourself.

    Your client seriously needs to rethink why holes in sequence numbers shouldn't be allowed.
  • 23. Re: Sequence Issue
    Tubby Guru
    Currently Being Moderated
    mkmunir wrote:
    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
    As you've previously pointed your client to this thread, hopefully they will read this.
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4343369880986

    A "Gapless" number requirement is really nothing more than a requirement for slow running, non-scalable systems.
  • 24. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    mkmunir wrote:

    you ge the maximum points for this answer if the solution works...
    If by works you mean slow Oracle performance down to a crawl then yes, it really will work.
  • 25. Re: Sequence Issue
    731280 Explorer
    Currently Being Moderated
    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.
  • 26. Re: Sequence Issue
    jihuyao Journeyer
    Currently Being Moderated
    increment by 1000 is good option here. get one sequence # and return 1000 for use.
  • 27. Re: Sequence Issue
    170763 Newbie
    Currently Being Moderated
    I tried your approch, it looked promissing, but it gave me a dead lok for three sessions runing the same function.....any suggestions.
  • 28. Re: Sequence Issue
    ToonKoppelaars Employee ACE
    Currently Being Moderated
    That's interesting...

    Could you paste the first part of the trace-file that was generated for this deadlock?
  • 29. Re: Sequence Issue
    6363 Guru
    Currently Being Moderated
    mkmunir wrote:

    I tried your approch, it looked promissing, but it gave me a dead lok for three sessions runing the same function..
    Hm, it supported up to three concurrent sessions, I'm actually pretty surprised it was that many.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points