Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003

    Unanswered: Better way to get random # of recs?

    I was playing around with PL/SQL in order to solve a problem. We need to retrieve a random sample of 1% of 1,000,000 recs. I was thinking about saving 10,000 random values - between 1 and 1 million - to a GLOBAL TEMPORARY TABLE, or saving the same values in a PL/SQL table.

    Then, I'd write a cursor against the 1 million record table, bringing in rownum, and then for each row check to see if that rownum was the 10,000 random values.

    Is there a method that ya'll use regularly that is better than this? I don't mind finishing it, it's fun to figure these things out, but I'd rather spend the time to implement a better sol'n.


  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    Oracle has already done that for you

    SELECT * 
    FROM theTable SAMPLE (1)
    SAMPLE(1) means return 1% of the data of the table (randomly chosen)

  3. #3
    Join Date
    Dec 2003
    Whoa ...
    You could call that better, yeppers.


  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    SAMPLE(1) will return approximately 1% of the data, e.g. maybe 99754 or 10,237 rows. That's probably fine for your needs. If it isn't you could do this:
    select * from
    (select * from mytable order by dbms_random.value)
    where rownum <= 10000;
    That will give you exactly 10,000 random rows.

    But SAMPLE(1) will be a lot more efficient, so use that if you can.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts