Here are all the details:
Source database 9.2.0.4 (upgrade from 8.1.6.3.4).
Target database 8.1.6.3.4.
View definition: create view my_view as select * from [EMAIL PROTECTED]
Sql: select * from local_table , my_view
where local_table.branch = 1
and my_view.customer = 200 +
Solved.
It was a hash join with the smaller table first but it pulled the whole 1M
records for this.
There was a: where local_table.branch = 1 in the query. I changed it into:
where remote_table.branch = 1 (there is an index on remote_table.branch) and
it came down to 2 seconds.
Thanks all.
Yechial,
It's been a couple of years since I worked on tuning queries with db links, but
a couple of issues come to mind:is the correct table being used for the inner
table of the join, is too much data being sent over the network.
-is the correct table being used for the inner table: I remember
What are the Oracle versions, settings
for optimizer_mode and full execution
plans.
Can you clarify
Remote table is a view with dblink.
Do you mean your query references a
local view which is a select from a remote
table; or does your query reference a view
at a remote site which is a
