Results of SAMPLE clause using a DBlink between 2 Oracle databases not randomised

dblinkoracleoracle-11g-r2

I'm using the SAMPLE clause to retrieve random rows from a table. It works fine except when the table is from another database. I'm using a database link between 2 Oracle 11g databases (11.2).

This query returns different random rows everytime I run it:

select * from myTable sample(10);

But this one always returns the same rows and in the same order:

select * from myTable@myDbLink sample(10);

I expected at least the same behavior as the tables are on the same version of Oracle. Why isn't it working? Is there a specific way to write this query so it can work with the link?

Best Answer

According to the Oracle documentation for the SELECT ... SAMPLE clause, the sample is random; it is just a coincidence that you are always getting the same rows on the local machine. You could try adding the SEED(x) clause, where x is a number from 0 to 4294967295; but that may not help going across a database link.

select * from myTable@myDbLink sample(10) seed(42);

Per the Oracle documentation:

SEED seed_value Specify this clause to instruct the database to attempt to return the same sample from one execution to the next. The seed_value must be an integer between 0 and 4294967295. If you omit this clause, then the resulting sample will change from one execution to the next.

Notice it is still not guaranteed!

Related Question