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

andreas.krisor_at_gmx.net (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?

why not select every nth row? Not exactly random, but it will select rows throughout the entire table, which would probably make it representative.

I can't seem to lookup the modulus function to check my syntax, but something like the following

	select * from
	( select *,rownum the_rownum from the_table)
	)
	where modulus(the_rownum,200)=1

You could use a RAND function if it existed to do a similar selection, but I don't see such a function in my index.

--

This space not for rent.