Home » Server Options » Replication » Materialized Views Help (Oracle 11g Relase 1)
Materialized Views Help [message #528595] Tue, 25 October 2011 22:14 Go to next message
rchenna
Messages: 25
Registered: February 2008
Junior Member
Hi,

I need help for following two questions.

1. I have created a materialized view with following syntax in 11g R1 database.

CREATE MATERIALIZED VIEW fr_emp_bonus_record_vw
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE+1/48
ENABLE QUERY REWRITE AS
SELECT person_id, <columns>

This MV will have around 300K records and this MV will be used in other queries based on person_id. So I have created an index on mv.person_id with following syntax. I have chosen REFRESH COMPLETE, I am assuming that, Oracle truncates this table and recreates it. When it recreates this MV, does it analyze automatically or do I need to do it manually.

CREATE UNIQUE INDEX mv_u1 ON mv(person_id);

2. It takes around 2 minutes to populate 300K. While it is populating the data, if somebody accessing this view or query where this view is being used, are they going to get any error?

Thanks in advance



Re: Materialized Views Help [message #528610 is a reply to message #528595] Wed, 26 October 2011 01:15 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
When it recreates this MV, does it analyze automatically or do I need to do it manually


Do it using dbms_stats.

Quote:
are they going to get any error?


No they will see and empty mview unless you change your parameters to refresh on demand and use DBMS_SNAPSHOT.REFRESH with option ATOMIC_REFRESH set to TRUE to refresh it.
In this case the other sessions will see the mview as it was before the refresh until the refresh completes.

Regards
Michel
Previous Topic: Pausing and Resuming of refreshing Materialised views
Next Topic: Replication options
Goto Forum:
  


Current Time: Thu Mar 28 12:42:17 CDT 2024