Re: Retrieving a set of randomly chosen records (Oracle 9i)

Andreas,

The SAMPLE is best, but if it is not good for you, you can try MOD. This example returns 1 row from every 100 rows.

SELECT columnA, columnB
  FROM your_table
 GROUP BY columnA, columnB
HAVING MOD(MAX(ROWNUM), 100) = 1 The result set is automatically ordered. The same rows are picked each time (if the table is not changed).

HTH Steve

andreas.krisor_at_gmx.net wrote:
> Hi,
>
> I need to analyze the data of very large tables (over 10 million
> records). Therefore I want to retrieve a set of randomly chosen
records
> (about 50,000).
>
> Question: Exists a predefined function in Oracle 9i, which fetches a
> certain part (e.g. 1%) of the rows of a table?
>
> Hint: Because I need to get all of the data in a random order and not
> in the way the rows are arranged in the table I cannot work with
SELECT
> * from xxx WHERE ROWNUM < 50001.
> But maybe the ROWID can be utilized to fetch a random sample of the
> rows?
>
> King regards,
>
> Andreas
Received on Sun Feb 20 2005 - 21:46:28 CST