1 2 Previous Next 23 Replies Latest reply on Jan 28, 2013 6:03 PM by Jonathan Lewis Go to original post
      • 15. Re: What is the possibility of getting the same random number repeated again?
        6363
        983902 wrote:
        mmm...I tried the same and there were duplicates in my case as well but I am still reluctant to use sequence!
        Why?

        The previous reason you gave makes no sense
        983902 wrote:
        I do not want a sequence because I need to support multiple tables with this ID generation and to scale up,
        is there any other way to create 5 digit number from Oracle?
        There are almost as many ways as you can think up

        {message:id=10469115}

        It just depends on your requirement, which is not very clear. Why does it need to be random? Does it need to be unique? If it is unique why do you care what the actual value is? What exactly do you think is the problem with using sequences for unique values to be used in multiple tables?
        • 16. Re: What is the possibility of getting the same random number repeated again?
          Jan-Marten Spit
          i doubt that Oracle support can answer questions that don't have an answer.

          This question could be answered:

          What is the -probability- of getting the same random number repeated again if i gerenate them a X/s, and do it for N seconds?

          and if you read the OP intention, he is not asking for probability, but certainty :)
          • 17. Re: What is the possibility of getting the same random number repeated again?
            Jan-Marten Spit
            use a md5 hash on something like the sysdate.

            DBMS_OBFUSCATION_TOOLKIT.MD5
            • 18. Re: What is the possibility of getting the same random number repeated again?
              986905
              it does not need to be random; can be any number (but 5 digits); In my application, I have got different workflows inserting/updating 24 different tables; each row in these tables needs an ID; During peak hours, we expect 3000 users to have logged into the application; at least 1000 users would be executing the workflows that might insert/update these 24 tables; so my ID generation at the minimum should generate 1000 unique ids for a given second. I am reluctant to use sequence because I am afraid there may long waits.

              I am kind of gave up on the random numbers.

              Thanks
              Venkatesh
              • 19. Re: What is the possibility of getting the same random number repeated again?
                JustinCave
                Your concern doesn't appear to make a lot of sense...

                Sequences are specifically designed to allow you to generate a large number of unique values in multiple sessions with a minimum amount of time and effort. It is exceptionally unlikely that you would be able to write code that allowed a number of sessions to generate a large number of unique values that would be any more efficient than using sequences. Generating random numbers (let alone generating the date and concatenating the results) will be much, much less efficient than using a sequence

                Using a sequence, you generate 3 consistent gets and 30 recursive calls
                SQL> create sequence seq_foo
                  2    cache 10000;
                
                Sequence created.
                
                SQL> select seq_foo.nextval
                  2    from dual
                  3  connect by level <= 10000;
                
                10000 rows selected.
                
                <<snip>>
                
                Statistics
                ----------------------------------------------------------
                         30  recursive calls
                          3  db block gets
                          3  consistent gets
                          0  physical reads
                        780  redo size
                     174304  bytes sent via SQL*Net to client
                       7849  bytes received via SQL*Net from client
                        668  SQL*Net roundtrips to/from client
                          1  sorts (memory)
                          0  sorts (disk)
                      10000  rows processed
                Just generating random numbers, you generate 10x more recursive calls and ~100x more consistent gets
                SQL> select dbms_random.value
                  2    from dual
                  3  connect by level <= 10000;
                
                10000 rows selected.
                
                <<snip>>
                
                Statistics
                ----------------------------------------------------------
                        419  recursive calls
                          0  db block gets
                        241  consistent gets
                          3  physical reads
                          0  redo size
                     344288  bytes sent via SQL*Net to client
                       7849  bytes received via SQL*Net from client
                        668  SQL*Net roundtrips to/from client
                          5  sorts (memory)
                          0  sorts (disk)
                      10000  rows processed
                Why do you believe that using sequences will create performance issues? Do you have any evidence that indicates you're going to have a problem?

                Justin
                • 20. Re: What is the possibility of getting the same random number repeated again?
                  986905
                  I started to believe that there could be performance issues after reading here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:2985886242221
                  • 21. Re: What is the possibility of getting the same random number repeated again?
                    JustinCave
                    That thread is discussing the difference between having one sequence per table and one sequence for every table. Yes, it will be more efficient to have one sequence per table rather than having one sequence for every table in the system because it means that there is less contention for that one latch. But a single sequence will still beat the pants off of any single centralized number generator that you would care to write. It would be better to create a sequence for each table rather than using a single sequence but a single sequence is better than anything you're likely to write yourself.

                    Justin
                    • 22. Re: What is the possibility of getting the same random number repeated again?
                      Mark Malakanov (user11181920)
                      I am fine with the repetition as long as it does not repeat within a second;
                      when we are talking about random values returned by pseudo-random computer functions it would not make sense to talk about a time intervals like one second.
                      With computer generated random numbers we are talking about a probability of getting same value. And this probability will vary not on period of time but rather on how many times you call this function.
                      For example if you call DBMS_RANDOM.VALUE(0, 99999) once per second a probability of getting same number will be 1/100000 for each second. If you call it 100000 times per second - probability will be 1.
                      • 23. Re: What is the possibility of getting the same random number repeated again?
                        Jonathan Lewis
                        Justin Cave wrote:

                        Just generating random numbers, you generate 10x more recursive calls and ~100x more consistent gets
                        SQL> select dbms_random.value
                        2    from dual
                        3  connect by level <= 10000;
                        
                        10000 rows selected.
                        
                        <<snip>>
                        
                        Statistics
                        ----------------------------------------------------------
                        419  recursive calls
                        0  db block gets
                        241  consistent gets
                        3  physical reads
                        0  redo size
                        344288  bytes sent via SQL*Net to client
                        7849  bytes received via SQL*Net from client
                        668  SQL*Net roundtrips to/from client
                        5  sorts (memory)
                        0  sorts (disk)
                        10000  rows processed
                        Justin,

                        You really need to run the statement a second time to eliminate the recursive SQL relating to parsing and loading the library cache before making this comparison.


                        Regards
                        Jonathan Lewis
                        1 2 Previous Next