Re: how to generate a randomized "seqno" column

"Cris" <cris119_at_operamail.com> a écrit dans le message de news: 45379793$0$12407$5fc30a8_at_news.tiscali.it...
|I have to write a pl/sql stored procedure that generates n rows
| identified by a progressive ID and with another column that gives the
| rows a unique, random seqno.
|
| E.g.:
|
| ID SEQNO
| -- -----
| 1 3
| 2 6
| 3 1
| 4 2
| 5 5
| 6 4
|
| The first silly idea that comes to my mind is to loop from 1 to n and,
| inside the loop, to call repeatedly dbms_crypto.randomInteger until he's
| able to find a new unique seqno.
|
| This looks to me like a performance killer. Are there more intelligent
| alternatives?
|
| Thank you. Kind regards,
|
| --
| spamto:cris119_at_operamail.com
|
| blind faith in your leaders or
| in anything will get you killed
|
|

You can do something like:

SQL> select id from t order by id;

        ID

         1
         2
         3
         4
         5
         6

6 rows selected.

SQL> select id,
  2 row_number() over (order by dbms_random.value) seqno   3 from t
  4 order by id
  5 /

        ID SEQNO
---------- ----------

         1          3
         2          1
         3          2
         4          6
         5          5
         6          4

6 rows selected.

Regards
Michel Cadot Received on Thu Oct 19 2006 - 11:02:12 CDT