Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    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.

    -Chuck

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Oracle has already done that for you

    Code:
    SELECT * 
    FROM theTable SAMPLE (1)
    SAMPLE(1) means return 1% of the data of the table (randomly chosen)
    http://download-west.oracle.com/docs...3a.htm#2065954

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

    Thanks
    -cf

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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:
    Code:
    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
  •