Re: ** outline change

   Thanks. Update is that finally working fine with doing update to UPDATE OUTLN.OL$HINTS.

Thanks Riyaj, Mark,Nigel and all for your help.

 Yes, this is a proper fast refresh MV with corresponding MV log. It was refreshing fine. Somehow it has started doing full table scan. Earlier I think outline for it was working. Other tables setup in similar way do a index access. Thanks

• It is using PK : "WITH PRIMARY KEY"
• The definition is simple : create MATERIALIZED VIEW ..... select col1, col2 .... from tabl1_at_source;
• It is not complicated and no issues creating the fast refresh MV.
• REFRESH FAST ON DEMAND

CREATE MATERIALIZED VIEW ACTIVITY_MV ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH FAST ON DEMAND WITH PRIMARY KEY AS

SELECT activity_id, create_date, .....

...

FROM ACTIVITY_at_ACT_SOURCE a

Thanks

• On Fri, 3/20/09, Jared Still <jkstill_at_gmail.com> wrote: From: Jared Still <jkstill_at_gmail.com> Subject: Re: ** outline change To: ajoshi977_at_yahoo.com Cc: riyaj.shamsudeen_at_gmail.com, ajoshi97_at_yahoo.com, oracle-l_at_freelists.org Date: Friday, March 20, 2009, 2:27 PM

On Wed, Mar 18, 2009 at 3:14 PM, A Joshi <ajoshi977_at_yahoo.com> wrote:

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 .

As the remote table has an MV log table associated with it, a fast refresh is available for many types of MV's.

At this point it might be a good idea to know the following.

What is the specified refresh method for the MV?

Does it use PK or ROWID?

If the MV is considered 'complex', it cannot be fast refreshed. http://download.oracle.com/docs/cd/B19306_01/server.102/b14226/repmview.htm#BABEEHGJ

Posting the code to create the MV might be a good idea.

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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