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

casey.kirkpatrick@gmail.c · *nix forums beginner Joined: 08 Jun 2005 Posts: 20

select * from table where DBMS_UTILITY.get_hash_value(ROWID ||
to_char(sysdate,'HH:MI:SS'),1,100) = 1

The "100" can be tweaked to give more or fewer rows.

andreas.krisor@gmx.net · *nix forums beginner Joined: 18 Feb 2005 Posts: 8

Hi Steve,

thank you for your suggestion but the statement runs about half an
hour, so it doesn't really work for me.

Greetz,
Andreas

SteveSutley@cox.net · *nix forums beginner Joined: 21 Feb 2005 Posts: 1

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@gmx.net wrote:

Malcolm Dew-Jones · *nix forums Guru Joined: 04 Mar 2005 Posts: 418

andreas.krisor@gmx.net (andreas.krisor@gmx.net) wrote:
: Hi Malcolm,

: I have at the moment no possibility to try your suggestion but I think
: the ROWNUM refers to the result set of a query, so a full table scan is
: used for this statement which reduces the performance dramatically and
: therefore wouldn't be applicable.

: Am I right?

maybe, I don't know for sure

However my suggestion was less than optimal, not sure what I was thinking.
I haven't tested the code but I'm pretty sure you don't need to select
from a select, simply

select * from table where rownum modulus whatever

That could in theory be optimized at the remote end to retrieve only a
some of the rows.

As for a full table scan (which I also responded to just above) if you
want any kind of randomish sample then surely you will have to scan the
whole table - the only issue is whether the remote end can do the scan so
as to reduce the amount of data that must be sent.

--

This space not for rent.

andreas.krisor@gmx.net · *nix forums beginner Joined: 18 Feb 2005 Posts: 8

Hi,

this probably works, but unfortunately I don't have the CREATE VIEW
priv in my schema.

andreas.krisor@gmx.net · *nix forums beginner Joined: 18 Feb 2005 Posts: 8

Hi Malcolm,

I have at the moment no possibility to try your suggestion but I think
the ROWNUM refers to the result set of a query, so a full table scan is
used for this statement which reduces the performance dramatically and
therefore wouldn't be applicable.

Am I right?

Greetz,
Andreas

andreas.krisor@gmx.net · *nix forums beginner Joined: 18 Feb 2005 Posts: 8

Hi Voker,

yes, it refers to another oracle-instance. Is there a difference
between the communication between 2 Oracle DB and an Oracle DB and for
example a DB2 DB?

Greetz,
Andreas

Malcolm Dew-Jones · *nix forums Guru Joined: 04 Mar 2005 Posts: 418

andreas.krisor@gmx.net (andreas.krisor@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.

IANAL_VISTA · *nix forums Guru Joined: 23 Apr 2005 Posts: 485

"andreas.krisor@gmx.net" <andreas.krisor@gmx.net> wrote in
news:1108737915.215801.132860@f14g2000cwb.googlegroups.com:

Volker Hetzer · *nix forums Guru Wannabe Joined: 04 Apr 2005 Posts: 214

<andreas.krisor@gmx.net> schrieb im Newsbeitrag news:1108737915.215801.132860@f14g2000cwb.googlegroups.com...

andreas.krisor@gmx.net · *nix forums beginner Joined: 18 Feb 2005 Posts: 8

Hi Norm,

thank you, but still I have a problem (see below).

Greetz,
Andreas

andreas.krisor@gmx.net · *nix forums beginner Joined: 18 Feb 2005 Posts: 8

Hi David,

thank you for this hint, it works partly:

my SELECT-statements refer to tables via a db-link and using SAMPLE
results in an error.

Do you know how I can use SAMPLE with db-links?

Thanks,
Andreas

IANAL_VISTA · *nix forums Guru Joined: 23 Apr 2005 Posts: 485

"andreas.krisor@gmx.net" <andreas.krisor@gmx.net> wrote in
news:1108736215.318122.236000@g14g2000cwa.googlegroups.com:

David Aldridge · *nix forums beginner Joined: 03 Feb 2005 Posts: 46

You can also try the SAMPLE clause in the select statement. SQL
Reference for details

Norman Dunbar · *nix forums beginner Joined: 09 Feb 2005 Posts: 24

andreas.krisor@gmx.net wrote:

Google