OraFAQ Forum: Replication » Materialized view

Members

Help

Home

Home » Server Options » Replication » Materialized view (Oracle -10G)

Show: Today's Messages :: Show Polls :: Message Navigator

E-mail to friend

Materialized view [message #395437]

Wed, 01 April 2009 07:58

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

Report message to a moderator

Re: Materialized view [message #395458 is a reply to message #395437]

Wed, 01 April 2009 08:56

Michel Cadot
Messages: 28991
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member

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

Kevin Meade
Messages: 1006
Registered: December 1999

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

Report message to a moderator