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

Question

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?

Answer 1

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!

• kageza: It seems you didn't understand. I'm getting the same rows on the distant machine, not the local one. And using seed is just the opposite of what I want; I want randomness.
• Mark Stewart: Hmmmm. Interesting. Different operating systems on the two machines, or perhaps different versions/patches of the operating systems?
• kageza: No, the DBlink is between 2 Oracle instances on the same OS, CentOS 5.6
• Mark Stewart: Only thing I can think of is maybe the patch levels are not exactly the same, or the environment variables for the oracle user id such as the PATH or LD_LIBRARY_PATH aren't the same, so the randomizing is differing. You may want to try setting the seed to a random number on the remote machine, such as using the dbms_random.value(1, 1024) like this: select * from myTable@myDbLink sample(10) seed(dbms_random.value(1, 1024));