CODEWITHSUNDEEP
sql-serveroracle-databasedblink
Jailani
Jailani

Reputation: 1

Oracle and SQL Server DBLink Performance Issue (Direct select from table@dblink vs create view)

I've successfully create dblink to access SQL Server 2000 from Oracle 12. I'm accessing this using Oracle PL/SQL. performing 

select * from table1@dblink where id=1

instantly produce output to pl/sql windows. But if I create view first and then perform select statement, the result significantly slower. 

"create view view1 as select * from table1@dblink;"
"select * from view1 where id=1"

from my understanding, it just select from the same table and I'm creating the view just to simplify the name.

Thank you.

Upvotes: 0

Views: 1967

Answers (1)

finidigeorge
finidigeorge

Reputation: 149

Looks like the problem you are facing is basically the site on which the query is running. When you run a query with tables accessed via dblinks Oracle has 2 options: it could retrieve all data from the dblink and then apply all filter and join conditions on Oracle's server or push those conditions to remote site (SQL server in your case) and retrieve already filtered information. You can check query plans to see the differences. Also you can control this behavior by using DRIVING_SITE hint, try to create the view with this hint, your query should work as fast as the first one:

create view view1 as select /*+ DRIVING_SITE(t)*/ * from table1@dblink t

Upvotes: 2

Related Questions