Home » RDBMS Server » Networking and Gateways » Where are predicates applied when linking Oracle to SQL Server (Oracle DB 12.x, SQL Server ???)
Where are predicates applied when linking Oracle to SQL Server [message #660469] Fri, 17 February 2017 12:50 Go to next message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
I have to apologize for a lack of information. If this makes my question impossible to answer, I'll try to get more.

We have an Oracle DB, with a database link that goes to a SQL Server database. I am told that if I run a query that addresses a table through the link, the entire table will be returned to Oracle, and only then will filter predicates be applied. Similarly, if I do an aggregation, the entire table gets transferred through the link and the aggregation occurs in the Oracle side.
Because of this, I'm being pushed to have the SQL Server people create a set of views that will do the filtering and aggregations, then I can SELECT * through the link.

Is that correct? Surely not! I had assumed that my queries would be sent to SQL Server and they would execute there, with only the result set returned.

I really don't want to work through views created for me, because I won't be able to change anything without going through the SQL Server guys. It is awkward setting up a test, for exactly that reason.

Thank you for any insight.
Re: Where are predicates applied when linking Oracle to SQL Server [message #660472 is a reply to message #660469] Fri, 17 February 2017 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think you can see the amount the space you received from the db link following the "bytes received via SQL*Net from dblink" statistic.
So executing a simple aggregate or filtered query that returns few lines you will be able to see if you get only the result or the whole table.

Re: Where are predicates applied when linking Oracle to SQL Server [message #660482 is a reply to message #660472] Sat, 18 February 2017 04:05 Go to previous messageGo to next message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
Thanks, that certainly measures the effect for an Oracle -> Oracle link. I'll try to get the developers to run a couple of tests Oracle -> SQL Server. It just takes forever to get anything done when one has to go through different groups.
Re: Where are predicates applied when linking Oracle to SQL Server [message #660492 is a reply to message #660482] Sat, 18 February 2017 16:02 Go to previous messageGo to next message
ThomasG
Messages: 3205
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What I have found, when querying SQLServer from Oracle, is that datatypes conversions an be quite a pain, and some other quirks that sometimes affect performance.

I often don't run the queries directly, but with DBMS_HS_PASSTHROUGH. That way I have more control on what exact query is sent to SQLServer.

Oh, yeah, and another thing that "SQLServer People" have to keep in mind: Readers can block writers. Which can be quite a pain, which is completely unheard of in the Oracle word.

[Updated on: Sat, 18 February 2017 16:04]

Report message to a moderator

Re: Where are predicates applied when linking Oracle to SQL Server [message #660499 is a reply to message #660492] Sun, 19 February 2017 04:39 Go to previous message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
Thank you for this, Thomas. I've put together a simple example that might work, and passed it over to the developers to test:
set serverout on

declare
v_cursor   binary_integer;
v_ename    varchar2(20);
begin
v_cursor := dbms_hs_passthrough.open_cursor@linktosqlserver;
dbms_hs_passthrough.parse@linktosqlserver(v_cursor,'select "ENAME" from "EMP"');
while dbms_hs_passthrough.fetch_row@linktosqlserver(v_cursor) > 0
loop
  dbms_hs_passthrough.get_value@linktosqlserver(v_cursor, 1, v_ename);
  dbms_output.put_line(v_ename);
end loop;
dbms_hs_passthrough.close_cursor@linktosqlserver(v_cursor);
end;
/
Previous Topic: ORA-12545: *only* with 32 bit client on Windows Server 2016
Next Topic: DG4ODBC taking a long time to fetch from HANA database over dblink with a where clause
Goto Forum:
  


Current Time: Thu Dec 14 07:54:56 CST 2017

Total time taken to generate the page: 0.01746 seconds