** query over db link behaving strange
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 25 Oct 2005 20:09:49 -0700 (PDT)
Hi,
I have a query selecting from a local table and a view which selects from
remote table. The query is doing a full table scan at remote site and taking
very long. This remote database is replicated. If I change the view to point
this replicated database of remote database the query chooses index there and
runs very fast. The query sent to the to this replicated copy of remote
database has this column with index specified in where clause. so it chooses to
use index there. I get this query using set autotrace traceonly explain. The
query sent to the (original) remote database does not have this column specifed
in where clause. The database and table are identical along with init
parameters. Only difference is that table on remote database was re-orged using
alter table .. move. so it could have different statistics. I tried exp/imp of
statistics from this problem database to another database but could not
reproduce the problem. Well...
So when query executes does it look at statistics or something else on
remote database to determine the access path? Answer from Oracle support is
that remote database statistics are not looked at when determining execution
plan. So what is happening here? Wy send different query to two databases for
the same initial query. The version is 8.1.7.4
The same query when run on remote database (it has view pointing to my local
table) runs fine using index. The same query with driving_site hint on my
database runs fine using the index. I cannot change the query to put in the
hint. it is generated so i need to fix this issue some other way. Thanks a lot
of your help. Thanks
---------------------------------
Yahoo! FareChase - Search multiple travel sites in one click.
Other related posts:
- » ** query over db link behaving strange
