Querying 'random' Oracle data via openquery in SQL Server returning the same data for multiple runs

Question

I'm asked to help a developer figure out why the "random" data we should be getting from Oracle is exactly the same results each time you run it, rather than being actually random. SQL 2012, Oracle 11g, linked server is using OLE DB connector type.

When they run the following query directly in the Oracle DB, they get proper "random" results. Through the linked server in SQL, they'll always get the same rows (and I've verified this by running the linked server query myself).

select * from openquery(ORA_DB, 'select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no 
 from qa_eligible_XX 
 where qa_XX_no = 4 
 and owner_code <> ''G'' 
 and last_trans_code = ''OL'' 
 and XX_cycle = ''90 - 120 Periodic'' 
 and XX_date < sysdate - 90 
 and rownum <= 200 
 order by dbms_random.value')

Note: I'm not an Oracle DBA but I work with one; he didn't see an issue with the Oracle part of the above query. Also, I edited a lot of stuff out and replaced with XX for privacy.

Each time we run this query in SSMS, we get the same 200 rows. I even connected from another SQL server that has the same linked server and ran this query, and got the same 200 rows (the same Oracle user is used for both connections). This leads me to believe Oracle may be caching the results; but somehow it's not doing this when the query's run in Oracle directly. Is there a way to force it to return new results through openquery? (so that the 200 rows it returns are a truly random sampling each time)

• Nic: What happens if you make the query a subselect, and move the rownum <= 200 outside of that? ie select * from (select <cols> from <tbl> where <predicates> order by dbms_random.value) where rownum <= 200 ? As stands it reads to me that it would get the first two hundred rows and then order those by the random value.
• Fredric Shope: Thanks Nic, looks like that does indeed work. Can you flesh out a full answer so I can mark it? And that begs the question; why does the query work as-is in Oracle (I'm guessing they were using SQLPlus) and not via linked server?

Answer 1

In the query you have it appears as though you are grabbing the first two hundred rows, and then sorting those by dbms_random.value.

This would be due to Oracle's query precedence which performs the query and then sorts the results. You would want to randomly sort the results first and then return the first two hundred rows. Using a subquery should provide you with the data that you want.

select * from openquery(ORA_DB, 'select * from 
 (
 select XX_NUMBER, XX_CODE, UNIT, XX_serial, XX_date, XX_area, building, location, XX_status, owner_code, qa_XX_no 
 from qa_eligible_XX 
 where qa_XX_no = 4 
 and owner_code <> ''G'' 
 and last_trans_code = ''OL'' 
 and XX_cycle = ''90 - 120 Periodic'' 
 and XX_date < sysdate - 90 
 order by dbms_random.value
 )
 where rownum <= 200 ')

Answer 2

Oracle classic. The ORDER BY occurs after the WHERE rownum <= 200