SQL Server OpenQuery Oracle Data Returns Same Results – How to Fix

linked-serveroraclesql server

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)

Best Answer

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 ')
Related Question