Home » Server Options » Replication » Materialized view (Oracle -10G)
Materialized view [message #395437] Wed, 01 April 2009 07:58 Go to next message
convey05
Messages: 43
Registered: December 2007
Location: CHENNAI
Member
Hi,
I have a Materialized view based on 2 views.I modified one column in both the views.Do i need to refresh the Materialized view.
Eg:

In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original

CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH TO_DATE('1-Mar-2009 20:09:28','dd-mon-yyyy hh24:mi:ss')
NEXT sysdate + 1/92
WITH PRIMARY KEY
AS
SELECT *
FROM view1
union
select * from view2;

Thanks in advance
Re: Materialized view [message #395458 is a reply to message #395437] Wed, 01 April 2009 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I think you should recreate the mview to prevent from having wrong data.

Regards
Michel

[Updated on: Wed, 01 April 2009 08:56]

Report message to a moderator

Re: Materialized view [message #396212 is a reply to message #395458] Sun, 05 April 2009 01:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Since your mview must use complete refresh, and since your columns are of the same datatype so that you have not changed the actual specification of the row, it may be possible to simply referesh the mview. A complete refresh of data will happen, oracle will re-execute the query, and your new values will show up.

Here is an example:

SQL> create table temp1 (a number,b number)
  2  /

Table created.

SQL> 
SQL> alter table temp1 add primary key (a)
  2  /

Table altered.

SQL> 
SQL> insert into temp1 values (1,2)
  2  /

1 row created.

SQL> 
SQL> commit
  2  /

Commit complete.

SQL> 
SQL> create or replace view v_temp1
  2  as
  3  select a,b,a c1 from temp1
  4  /

View created.

SQL> 
SQL> create materialized view mv_temp1
  2  as
  3  select *
  4  from v_temp1
  5  /

Materialized view created.

SQL> 
SQL> select * from mv_temp1
  2  /

         A          B         C1
---------- ---------- ----------
         1          2          1

SQL> 
SQL> create or replace view v_temp1
  2  as
  3  select a,b,b c1 from temp1
  4  /

View created.

SQL> 
SQL> select * from mv_temp1
  2  /

         A          B         C1
---------- ---------- ----------
         1          2          1

SQL> 
SQL> exec dbms_mview.refresh('MV_TEMP1')

PL/SQL procedure successfully completed.

SQL> 
SQL> select * from mv_temp1
  2  /

         A          B         C1
---------- ---------- ----------
         1          2          2

SQL> 


I would not be surprised if in the past Oracle simply invalidated such an mview when its underlying objects were modified, but regardless, it is smart enough today to understand that a combination of factors makes this rebuild without recreate possible.

Maybe next time you can try to invent such a test case yourself? Alternatively you might simply have tried to refresh the mview and see what happened.

Good luck, Kevin
Re: Materialized view [message #396245 is a reply to message #396212] Sun, 05 April 2009 12:57 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Thanks Mr Kevin. It's good explanation.

Yes. You need recreate your materialized view.

Babu
Previous Topic: Sub-query in Materialized Views
Next Topic: Database Synchronisation.
Goto Forum:
  


Current Time: Thu Mar 28 16:36:57 CDT 2024