Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Retrieving a set of randomly chosen records (Oracle 9i)

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

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 18 Feb 2005 14:25:16 -0800
Message-ID: <42166b4c@news.victoria.tc.ca>


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.
Received on Fri Feb 18 2005 - 16:25:16 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US