we are writing a query in oracle which needs to gets data from joining two tables which are in different databases located on different servers.
For this, we are using Dblinks in the query . But, this is causing the query to execute very slowly.
Is there any other way to do it instead of using Dblinks?
Of course, a materialized view (as proposed) would solve your problem. But sometimes MV's are to expensive. I wonder, if you could use a "with statement" referencing the remote DB Table.
The actual Select statement then uses this with statement once and must be build from inside out like this:
with XXX as (
Select stuff from foo_table@bar_link
)
select * from XXX left join your_local_table using (your key)
If your Oracle is configured correctly, it should execute the "expensive" part of loading foreign data only once. So you spare the DB-Drivespace for a MV... Give it a try! It is important to do NOT use stuff from your remote DB in the Where statement. Because then sometimes, your remote DB is queried every row your are checking in the where clause...
Good luck
