Fwd: ** outline change

Isn't the problem here that you are refreshing an mview on database T(arget) using a query which is running on database S(ource). The query is shipped as is to S, which acts as the driving site (I think). So for any query rewrite to happen, you would have to do it on S, the source. Which you mentioned you only have read privileges for (not DBA).

Time to talk to the DBA for that database?

Alternatively - and to my mind, a lot simpler: just tune the mview's SQL in the normal way, rather than by query rewrite. So start with the SQL and see if there is an alternative way of getting the same result without the offending FTS. I don't think we've seen the query or the (unsatisfactory) plan yet...

Regards Nigel

2009/3/18 A Joshi <ajoshi977_at_yahoo.com> Riyaj,

   Thanks for your help. tkprof shows full table scan on the remote database. So even if the mlog has 10 entries : it is doing full table scan on source table which is big and fts is taking almost one hour . It does not do a simple index search which will take 1 minute. I was able to get the big query which uses current$ and mlog$. It has a bind variable 1 (:1) and I am not able to create a bind variable with 1

>
>
>

--
http://www.freelists.org/webpage/oracle-l