** query over db link behaving strange

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 Received on Thu Oct 27 2005 - 11:53:28 CDT