| 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
|
|
|
|
|
|
| Re: Materialized view [message #396212 is a reply to message #395458] |
Sun, 05 April 2009 01:25  |
 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
