Join SQL Server table in OPENQUERY on multiple keys

Question

I need to join an Oracle linked server table (with 1 billion rows) with an SQL Server table (with 100 rows) on multiple keys:

SELECT * FROM OPENQUERY(EDW, 'SELECT * FROM ORACLE_TABLE') A
INNER JOIN SQL_TABLE B
ON A.KEY1 = B.KEY1 AND A.KEY2 = B.KEY2
INNER JOIN SQL_TABLE C
ON A.KEY3 = C.KEY1 AND A.KEY4 = C.KEY2;

But ORACLE_TABLE is very huge and this query takes too much time to run. Is there a way to pass SQL_TABLE to OPENQUERY and do the join on the Oracle server?

Answer 1

OK, then think the other way around. Something like the general outline:

1. Create a Linked Server from Oracle to SQL Server and SQL Server to Oracle.
2. From Oracle select the 100 rows of SQL Server data and store in a temporary table in Oracle.
3. Run the query in Oracle to join/filter/whatever the 1 billion Oracle rows by the 100 SQL Server rows in the temporary table to create the result set.
4. Use OPENQUERY from SQL Server to bring the results back to SQL Server.

INSERT INTO SqlServerTable 
 SELECT * FROM OPENQUERY (LinkedServerName, 'query text');

I likely missed some step, but this basic approach is to move the small amount of SQL Server data over to Oracle, run the query, and then read the result back to SQL Server.

EDIT: At https://stackoverflow.com/questions/29200147/how-do-i-convert-an-oracle-timestamp-data-type-to-sql-server-datetime2-data-type Jon of All Trades showed how he converts an Oracle timestamp to a SQL Server datetime2.

(I have no Oracle server to test against, however.)

• Fan: That is a good solution. But what if I cannot modify Oracle database (usually IT does not allow the users to touch EDW). Is there an alternative?
• RLF: The main thing is to get the processing done where the great majority of data exists. Perhaps the Oracle server admins would create a temporary table for your process. Or if the 100 datum from SQL Server were small enough you might be able to include the data in the query. (OPENQUERY has an 8K limit for the query text.)
• Fan: This timestamp problem will happen in a1ex07's solution (when directly querying the Oracle table). But it will not happen in your solution (when using OPENQUERY).

Answer 2

You can try and check if REMOTE hint works for you.

SELECT * FROM SQL_TABLE B
INNER REMOTE JOIN linkedServerName.dbname.schema.ORACLE_TABLE A
ON A.KEY1 = B.KEY1 AND A.KEY2 = B.KEY2
INNER JOIN SQL_TABLE C
ON A.KEY3 = C.KEY1 AND A.KEY4 = C.KEY2

• RLF: Reviewed a thread in SQLServerCentral on SQL Server linking to Oracle using the REMOTE hint. The success was varied, but it apparently did work for some cases. You could try that. If you access SQLServerCentral look at sqlservercentral.com/Forums/Topic1089660-1044-1.aspx
• Fan: I cannot query ORACLE_TABLE in this way. It has a TIMESTAMP column and SQL Server gives an "invalid metadata" error even if I do not select that column. However, that is another issue. I could ask IT to create a view. But again in my case it is extremely difficult to have IT do any changes on Oracle side.
• a1ex07: @user2316040 : I can't try myself, but what if you INNER REMOTE JOIN (SELECT all_columns_except_timestamp_column FROM ...) A ? I hope it shouldn't complain about timestamp then...
• Fan: @a1ex07 This is actually one of the first things I tried. Unfortunately it did not work. More details about this TIMESTAMP problem can be found in stackoverflow.com/questions/29200147/…