Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View not refreshing properly
Materialized View not refreshing properly [message #435172] Mon, 14 December 2009 23:20 Go to next message
vikram1780
Messages: 222
Registered: January 2007
Location: Bangalore
Senior Member
Hi,

As materialized view is not refreshing properly. we compare the sum of hashcode materialized view with the sum of hash code of the base view or table the materialized view is defined. If it's found equal then materialized view got refreshed succesfully else not.

But in below example the column value is interchanged in between the row eventhough hashcode is same.

Is there any other way to find the materialized view got refreshed successfully or not.


 
SQL> create table temp as select object_name, subobject_name from user_objects where rownum<3;
 
Table created
 
SQL> select * from temp;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME
-------------------------------------------------------------------------------- ------------------------------
ATP_BN_I_1                                                                       
ATP_START_END_DATE_I_2                                                           
 
SQL> create materialized view mv1 as select * from temp;
 
Materialized view created
 
SQL> select * from mv1;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME
-------------------------------------------------------------------------------- ------------------------------
ATP_BN_I_1                                                                       
ATP_START_END_DATE_I_2                                                           
 
 
SQL> select sum(nvl(ora_hash("OBJECT_NAME"||"SUBOBJECT_NAME"),0)) from temp;
 
SUM(NVL(ORA_HASH("OBJECT_NAME"
------------------------------
                    4852176374
 
SQL> select sum(nvl(ora_hash("OBJECT_NAME"||"SUBOBJECT_NAME"),0)) from MV1;
 
SUM(NVL(ORA_HASH("OBJECT_NAME"
------------------------------
                    4852176374
 
SQL> UPDATE TEMP SET OBJECT_NAME=SUBOBJECT_NAME,SUBOBJECT_NAME=OBJECT_NAME;
 
2 rows updated
 
SQL> COMMIT;
 
Commit complete
 
SQL> select * from mv1;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME
-------------------------------------------------------------------------------- ------------------------------
ATP_BN_I_1                                                                       
ATP_START_END_DATE_I_2                                                           
 
SQL> select * from temp;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME
-------------------------------------------------------------------------------- ------------------------------
                                                                                 ATP_BN_I_1
                                                                                 ATP_START_END_DATE_I_2
 
SQL> select sum(nvl(ora_hash("OBJECT_NAME"||"SUBOBJECT_NAME"),0)) from MV1;
 
SUM(NVL(ORA_HASH("OBJECT_NAME"
------------------------------
                    4852176374
 
SQL> select sum(nvl(ora_hash("object_name"||"subobject_name"),0)) from temp;
 
select sum(nvl(ora_hash("object_name"||"subobject_name"),0)) from temp
 
ORA-00904: "subobject_name": invalid identifier
 
SQL> select sum(nvl(ora_hash("OBJECT_NAME"||"SUBOBJECT_NAME"),0)) from temp;
 
SUM(NVL(ORA_HASH("OBJECT_NAME"
------------------------------
                    4852176374


Any help really appreciated


Thanks in advance

Re: Materialized View not refreshing properly [message #435173 is a reply to message #435172] Mon, 14 December 2009 23:29 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Have you searched anywhere, or even this forum, before posting your question here?

regards,
Delna
Re: Materialized View not refreshing properly [message #435181 is a reply to message #435172] Tue, 15 December 2009 00:35 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As even on your previous post..

I recommend this..
http://www.psoug.org/reference/materialized_views.html,

This http://www.dba-oracle.com/art_9i_mv.htm
And this http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10732/repmview.htm#29854.

try this
SQL>  select MVIEW_NAME||','||REFRESH_MODE||','||REFRESH_METHOD
  2   from user_mviews;

MVIEW_NAME||','||REFRESH_MODE||','||REFRESH_ME
----------------------------------------------
MV1,DEMAND,FORCE


so read about refresh_mode.....And even If the temp table has any primary key you will get...

ORA-01407: cannot update ("USER"."OBJECT_NAME"."COLUMN_NAME") to NULL



sriram Smile

[Updated on: Tue, 15 December 2009 00:41]

Report message to a moderator

Previous Topic: PIVOT - sum( case statement) when joining giving wrong results
Next Topic: BULK DELETE
Goto Forum:
  


Current Time: Tue Sep 27 11:05:51 CDT 2016

Total time taken to generate the page: 0.17523 seconds