andreas.krisor (AT) gmx (DOT) net wrote:

try this :

SELECT <stuff>
FROM <table>
SAMPLE(1);

The number in brackets (1) is the percentage of the table you want. You
will usually get a different sample on each execution. You cannot
guarantee that you will always get the same number of rows in the sample
though.


Cheers,
Norm.

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

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

"andreas.krisor (AT) gmx (DOT) net" <andreas.krisor (AT) gmx (DOT) net> wrote in
news:1108736215.318122.236000 (AT) g14g2000cwa (DOT) googlegroups.com:

Go lookup the keyword "SAMPLE" as part of the SELECT statement

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

Hi Norm,

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

Greetz,
Andreas

<andreas.krisor (AT) gmx (DOT) net> schrieb im Newsbeitrag news:1108737915.215801.132860 (AT) f14g2000cwb (DOT) googlegroups.com...

Greetings!
Volker

"andreas.krisor (AT) gmx (DOT) net" <andreas.krisor (AT) gmx (DOT) net> wrote in
news:1108737915.215801.132860 (AT) f14g2000cwb (DOT) googlegroups.com:

I can't say that I've tried this, but does it work to create a VIEW
in the remote DB which contains the SAMPLE clause?

SELECT * FROM REMOTE_VIEW@OTHER_DB;

andreas.krisor (AT) gmx (DOT) net (andreas.krisor (AT) gmx (DOT) 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.

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