April 6, 2011 at 7:00 pm
We have a query that runs on the SQL server side of things that needs to join a table of local, SQL server data to data retrieved over a linked server.
The join is fairly slow because it has to pull the entire table from the oracle side.
As the data on the SQL side is much smaller, it would be nice if there were a way to pass to oracle the join criteria so narrow the result set down before pulling it over to SQL. Is there a way to do something like this, or is it a pipe dream?
April 6, 2011 at 11:16 pm
You can try using the REMOTE join hint that tells SQL Server to try to run the process on the right side of the join. Here is an example:
SELECT
Local_Table.Col1,
Remote_Table.Col2
FROM dbo.Local_Table Local_Table
INNER REMOTE JOIN LinkedSever..OracleDB.Remote_Table Remote_Table
ON Local_Table.Col1 = Remote_Table.Col1;
It won't always work, but it certainly helps me some of the time.
April 6, 2011 at 11:20 pm
Another option if you just need a WHERE clause on the Oracle side is to use OPENQUERY:
SELECT
Local_Table.Col1,
Remote_Table.Col2
FROM dbo.Local_Table Local_Table
INNER JOIN OPENQUERY(LinkedSever, 'SELECT * FROM OracleDB.Remote_Table WHERE Col3 = 42') Remote_Table
ON Local_Table.Col1 = Remote_Table.Col1;
Of course that gets messy when you have character columns, double quotes, or want to use variables in the condition, but it can be done.
April 9, 2011 at 9:02 am
Unfortunately the remote join hint didn't work. Thanks anyway though. 🙂
June 10, 2011 at 4:53 pm
I would believe that remote join operations will only be possible if the remote db is also a SQL server db. In this case where the remote db is Oracle, the hint will be ignored.
June 13, 2011 at 10:56 am
carsten.jorgensen (6/10/2011)
I would believe that remote join operations will only be possible if the remote db is also a SQL server db. In this case where the remote db is Oracle, the hint will be ignored.
The hint isn't ignored, though the capabilities might be more limited. I know that adding the REMOTE join in a query that combined a driver table in SQL Server, and a large data table on Oracle caused SQL Server to send the keys to Oracle so that only the matching data was returned. (Before I added the REMOTE join hint the entire Oracle table was brought over to SQL Server to be filtered.)
June 14, 2011 at 6:12 am
gets the data first on the SQL Server side and then apply join on it, means insert data in a temp table then apply join
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply