Re: generating (unique) random numbers for a column

Jim Kennedy wrote:
>
> You are trying to generate random numbers without replacement. dbms_random
> cannot do that; It would have to remember all the numbers it generated to do
> that. If you know how many rows you could create a table with that many
> rows and an unique constraint and fill that table with values. Then use
> that series until you finish. That is make your own list of numbers that
> are random and unique.
> Jim
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can figure
> it out.
> "Glen A Stromquist" <glen_stromquist_at_no.spam.yahoo.com> wrote in message
> news:Xidra.1$yv1.4080_at_news2.telusplanet.net...
> > I am trying to populate a column in a table with random numbers with
> > roughly the same range as rows in the table, I want these unique so have
> > added the constraint. I have a simple trigger that generates a random
> > number before insert so when I load the table with sqlldr the numbers
> > are generated.
> >
> > My problem is that 50 or so of the 500 records get left out because the
> > constraint is violated when the dbms_random.value(<range>) tries to pick
> > a number it has already generated.
> >
> > the trigger is as follows:
> >
> > CREATE OR REPLACE TRIGGER "schema"."GEN_RAND" BEFORE INSERT OR
> > UPDATE OF "FIRST_NAME", "LAST_NAME", "ENTERED", "RANDOM"
> > ON "COMPANY_LIST"
> > FOR EACH ROW
> > declare
> > x number;
> > begin
> > x :=dbms_random.value(1,600);
> > :new.random := x;
> > end;
> >

If you check out some of the random number generators on the 'net you can find some well-defined linear congruential ones that with give at least uniqueness within a specified period, ie, you are guaranteed at least 'n' unique numbers before the numbers are re-cycled.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"