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 i
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.
Yec
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 + local_table.br
Yechiel Adar wrote:
>
> I did a select like:
>
> select name from local_table , remote_table
> where local_table.account = remote_table.account.
>
> Remote table is a view with dblink.
>
> I select about 100 records out of about 1M records at the remote db.
>
> I found out that oracle does ful
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 simpl
I did a select like:
select name from local_table , remote_table
where local_table.account = remote_table.account.
Remote table is a view with dblink.
I select about 100 records out of about 1M records at the remote db.
I found out that oracle does full table scan at the remote site.
I will we
