| Where are predicates applied when linking Oracle to SQL Server [message #660469] |
Fri, 17 February 2017 12:50  |
John Watson
Messages: 8964
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 #660492 is a reply to message #660482] |
Sat, 18 February 2017 16:02  |
ThomasG
Messages: 3212
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 |
John Watson
Messages: 8964
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;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
