0

I am querying a linked Oracle server from a local SQL Server instance using openquery. The issue is that I want to limit the results returned based on a table in the local DB (about 10k rows) while executing in the linked instance. Normally I would do it the other way around and do something like:

SELECT * 
FROM OPENQUERY(DBlinked, 'SELECT…') link_serv
    INNER JOIN localdb.table1 local_1 
    ON local_1.column_1 = link_serv.column_1

but because of the size of the linked tables being hit during the openquery (multiple tables with 100’s millions of rows) executing against the entire dataset then sub-setting locally with a join isn’t a good idea.

I’ve tried to reference back to the local server inside openquery but since the table is located on the local file it won't find the table, and I do not have write permission on the linked server so moving the local table over to the linked server would be problematic.

Any guidance on how to limit the results prior to all the joins would be appreciated. Using openquery is not a requirement -- its just the only elegant solution I've found for dealing with resource intensive queries against large linked tables.
Essentially what I’m trying to do is

SELECT * FROM OPENQUERY(oracleDB, '
    SELECT * 
    FROM dbo.table1 Ot
INNER JOIN Local_SQLServ.DB1.DBO.Table1 St
ON St.Column1 = Ot.column1’)
JWheeler
  • 320
  • 4
  • 14
  • Can you do it this way instead?: http://stackoverflow.com/questions/21137592/sql-linked-server-join-query or like this? http://stackoverflow.com/questions/12285228/how-to-join-linked-server-table-and-sql-server-table-while-using-openquery – Tab Alleman Dec 02 '15 at 20:44
  • @Tab Thanks for the response. The first link recommends a distributed query, but because of the size of the tables involved I don't think its a realistic option. Pretty much all of the work is being done on the linked oracle server I just only need it for the 10k records located on the local DB. The second link is essentially my first code snippet. Although both would work they also return the full datasets then limit results afterwards on the local DB. I'm hoping to find a more elegant solution then pulling 5-7, 100 million row tables over. – JWheeler Dec 02 '15 at 20:59
  • Could you build an `IN()` with the ID's (or whatever) from the local table? – Steve Dec 02 '15 at 22:15

0 Answers0