0

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?

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!

4
  • 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. – kageza Dec 16 '14 at 17:24
  • Hmmmm. Interesting. Different operating systems on the two machines, or perhaps different versions/patches of the operating systems? – Mark Stewart Dec 16 '14 at 18:21
  • No, the DBlink is between 2 Oracle instances on the same OS, CentOS 5.6 – kageza Dec 19 '14 at 12:09
  • 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)); – Mark Stewart Dec 19 '14 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.