0

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)

Improve this question
2
  • 1
    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.
    – Nic
    Nov 30, 2016 at 21:00
  • 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?
    – Fredric Shope
    Nov 30, 2016 at 22:14

2 Answers 2

Reset to default
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 ')
Improve this answer
0

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

Improve this answer

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.