1

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?

Improve this question

2 Answers 2

Reset to default
2

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.)

Improve this answer
3
  • 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? Commented Feb 3, 2016 at 15:13
  • 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.) Commented Feb 3, 2016 at 15:54
  • 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). Commented Feb 3, 2016 at 16:32
0

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
Improve this answer
4
  • 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 Commented Feb 3, 2016 at 16:06
  • 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. Commented Feb 3, 2016 at 16:08
  • @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... Commented Feb 3, 2016 at 16:12
  • @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/… Commented Feb 3, 2016 at 16:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.