Re: how to generate a randomized "seqno" column

Michel Cadot schrieb:
> "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
>
>

Very nice approach!

Best regards

Maxim Received on Thu Oct 19 2006 - 12:28:10 CDT