CODEWITHSUNDEEP
oracle-databaseoracle11gdblink
Gary
Gary

Reputation: 4725

Oracle: performance about filtering results from remote view

I have a remote database A which has a view v_myview. I am working on a local database, which has a dblink to access v_myview on databse A. If I query the view like this :

select * from v_myview @ dblink ;

it returns half million rows. I just want to get some specific rows from the view,e.g., to get rows with id=123, my query is 

select * from v_myview @ dblink where id=123;

This works as expected. Here comes my question, when I run this query, will remote database generates the half million rows first then from there to find rows with id=123? or the remote view applies my filter first then query the DB without retrieving the half million rows first? how do I know that. Thank you!

Upvotes: 0

Views: 591

Answers (1)

Justin Cave
Justin Cave

Reputation: 231741

Oracle is free to do either. You'd need to look at the query plan to see whether the filtering is being done locally or remotely.

Presumably, in a case as simple as the one you present, the optimizer would expect it to be more efficient to send the filter to the remote server rather than pulling half a million rows over the network only to filter them locally. That calculation may be different if the optimizer expects the unfiltered query to return a single row rather than half a million rows and it may be different if the query gets more complicated doing something like joining to a local table or calling a function on the local server.

Upvotes: 3

Related Questions