meterialized view refresh
Sreeju, November 10, 2002 - 10:48 pm UTC
Thanks for your valuable comments again and again. I have a problem with materialized view refresh. Have created a single table aggregate materialized view as shown below. In temp detail there are around 10 million records. There are cases where 100,000 records in the log(updates in detail table). When doing a fast refresh, it takes so long (more than 12 hours). But when i recreate the summary snapshot it takes only less than hour. There are indexes on all the seq_id columns (both in the detail and summary). Is there any way out to reduce the refresh time of fast refresh ?
Kindly request your expert advise ;
CREATE MATERIALIZED VIEW LOG ON TEMP_DETAIL
WITH ROWID
( CALENDAR_SEQ_ID
,MODEL_SEQ_ID
,SHOP_SEQ_ID
,MODULE_SEQ_ID
,CUST_SEQ_ID
,ORDER_NUM
,WEEK_SEQ_ID
,ACTUAL_HOURS
) INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW TEMP_SUMMARY
BUILD IMMEDIATE
REFRESH FAST
AS SELECT
CALENDAR_SEQ_ID
,MODEL_SEQ_ID
,SHOP_SEQ_ID
,MODULE_SEQ_ID
,CUST_SEQ_ID
,ORDER_NUM
,WEEK_SEQ_ID
,SUM(ACTUAL_HOURS) as ACTUAL_HOURS
,COUNT(*) as COUNT_GRP
,COUNT(ACTUAL_HOURS) as count_ACT
FROM TEST.TEMP_DETAIL
GROUP BY
CALENDAR_SEQ_ID
,MODEL_SEQ_ID
,SHOP_SEQ_ID
,MODULE_SEQ_ID
,CUST_SEQ_ID
,ORDER_NUM
,WEEK_SEQ_ID
November 11, 2002 - 10:37 am UTC
then it sounds like doing a complete refresh in your case is better then a changes only refresh.
If you are interested, you can update a couple of detail records, commit, enable sql trace and do the changes only refresh. You'll be able to use tkprof to format the trace file and SEE the sql that is needed in order to maintain this. Multiply that sql by 100,000 and you'll see why it takes that long.
Consider this -- suppose it takes a little under 0.5 seconds to process a single row refresh (I see sum's and counts and such - you are aggregating many records from the details into one for the MV). Well, 100,000 row updates at 0.5 seconds would be a little over 12 hours.
Maybe you need to
a) refresh more often so as to not build up such a large backlog
b) use complete refreshes (they are not evil, they are an option)
Dave, November 11, 2002 - 11:10 am UTC
Would I be right in inferring from this "deferrable constraints" tip that we should avoid the use of index-organized tables (where PK is non-deferrable) in materialized views?
Can you give more information on the situations in which the PK might have to be deferrable in order to avoid the unique contraint violation? I'm guessing that it might be something that could occur on fast refreshes, maybe not so on complete refreshes. Maybe I could stick with my IOT/MV's as long as I avoid particular MV features?
November 11, 2002 - 6:14 pm UTC
in the case of a fast refresh (changes only) that might be the case -- yes. a complete refresh -- no problem.
It would be very rare for a primary key to cause the issue (in fact, I cannot think of a case offhand where it would) -- a unique constraint, yes -- but not a primary key, since the primary key is what we use to apply the changes by in the first place.
Create deferrable PK contraint on materialized view?
Dave, May 23, 2003 - 4:52 am UTC
Tom,
I've looked for syntax on this but have been unable to find it & needless to say when I add a unique index on the materialized view, the corresponding constraint created gets violated during refresh. Can you please show me an example of creating a deferrable PK constraint on an mview?
Also, am considering fast or complete refresh for a materialized view with approx 3 million rows in it. Due to not satisfying the oracle 'rules' for a fast refresh, I may be forced to use a complete refresh - have no problem with using the complete refresh as it removes reliance on the these 'rules' allowing more flexibility in the underlying sql statement - it just seems the complete refresh is lot less elegant given the complete refresh essentially truncates then rebuilds the whole mview & indexes (most of which are bitmap). Total rebuild time for this mview is approx 2hr which I can do overnight in a batch job? Is it acceptable practice to use the complete refresh?
May 23, 2003 - 8:50 am UTC
you cannot put unique indexes on MVs, only "indexes".
the MV already has a primary key constraint and that won't be violated.
I would need to see a small (tiny) example of your issue unless I've missed the point.
with bitmaps, a complete refresh would probably be preferrable. bitmap indexes despise row by row operations.
Victor, May 23, 2003 - 10:42 am UTC
Indexes on mviews...
Kashif, October 18, 2005 - 9:46 am UTC
Hi Tom -
You said: you cannot put unique indexes on MVs, only "indexes". However:
kkashif@DVTDW01> create table t as select * from user_objects;
Table created.
kkashif@DVTDW01> alter table t add primary key (object_id);
Table altered.
kkashif@DVTDW01> create materialized view mv_on_t as select * from t;
Materialized view created.
kkashif@DVTDW01> create unique index mvt_idx on mv_on_t (data_object_id);
Index created.
Also, can we create unique indexes on materialized views, and have them somehow be deferrable, similar to how we can make constraints deferrable? The issue I am having right now is that there are several unique indexes defined on some mviews, and every once in a while they get violated at refresh time. For unique constraints I simply recreate them as deferrable, but I can't for unique indexes. Thanks.
Kashif
October 18, 2005 - 9:49 am UTC
you cannot put them on and expect it to actually always work. Hows that - that describes it better.
why:
....
During a refresh, the data can go temporarily "out of constency". When we are
done -- it'll be consistent.
A refresh will issue "set contraints all deferrable" before it begins --
allowing this temporary inconsistency to take place and upon commit the data
will be consistent.
Note that no other session other then the refresh session itself will ever see
"duplicate" data.
Ok...
Kashif, October 18, 2005 - 10:15 am UTC
Thanks Tom. What about unique indexes, is there a way they can be made "deferrable", similar to deferrable constraints?
Kashif
October 18, 2005 - 10:17 am UTC
you use a deferrable unique constraint :)
it'll utilize a non-unique index.
unique indexes are -- unique. period.
refresh on demand meterialized view
A reader, February 17, 2011 - 10:04 am UTC
Hi, I have a primary database and a replicated secondary(active read only) using data guard. I want to use secondary dedicated for reporting. I have defined some on demand Materialized views one primary. However, I do not want them to be refreshed on primary.
1. Can I use the DBMS_MVIEW.REFRESH to refresh ONLY on secondary leaving the primary untouched?
2. Will this affect primary commit time in anyway?
Thanks a lot.
February 17, 2011 - 11:53 am UTC
1) not if this is a physical standby, physical standbys are bit for bit copies of the source. If they are a logical standby - yes, we can have additional indexes and materialized views that do not exist on the source.
2) see #1 - it would not in a logical standyby and cannot be done in a physical standby
can materialized view consume more SGA,
Dipika, June 22, 2018 - 1:42 pm UTC
Hello Tom,
I'm also having doubt regarding materialized view,if i create mview from large loading sql query then mview will consume much SGA or i can say it will reserve some SGA for itself so other queries will not have enough utilization of memory parameter? and other queries will load more than usual?
June 24, 2018 - 10:37 am UTC
A materialized view is ultimately just another table. It will fall under the same standard buffer cache management scheme as any other table in your database.
can materialized view consume more SGA,
Dipika, June 26, 2018 - 6:36 am UTC
Hello Tom,
Thanks for your valuable reply,I'm Very new to Materialized view so again bit confused to what parameters i have use to create mview,server's db size already grown upto 300gb,and SGA parameter set to 600GB,I'm having one application page which is having 6 large sql query reports,so page loading time is 33 seconds so i want to create mview from them 6 reports,for performance purpose,so can you please suggest how do i create mview and what parameters do i need to use in this case. do i need to generate mview logs?or do i need to refresh it manually or automatically?