Re: ** outline change
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Mon, 6 Apr 2009 12:32:39 -0700 (PDT)
Jared,
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@source;
* It is not complicated and no issues creating the fast refresh MV.
* REFRESH FAST ON DEMAND
definition :
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@ACT_SOURCE a
Thanks
--- On Fri, 3/20/09, Jared Still <jkstill@xxxxxxxxx> wrote:
From: Jared Still <jkstill@xxxxxxxxx>
Subject: Re: ** outline change
To: ajoshi977@xxxxxxxxx
Cc: riyaj.shamsudeen@xxxxxxxxx, ajoshi97@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
Date: Friday, March 20, 2009, 2:27 PM
On Wed, Mar 18, 2009 at 3:14 PM, A Joshi <ajoshi977@xxxxxxxxx> 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
Other related posts:
