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

Hi,

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

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

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 (DOT) net wrote:

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

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.