Home » Server Options » Replication » Materialize view complete refresh
Materialize view complete refresh [message #63515] Tue, 12 October 2004 05:23 Go to next message
Joan
Messages: 36
Registered: February 2002
Member
How does Oracle handle a complete refresh of a materialize view?

I need to create a materialize view but since is join two tables with where clause is regarded as complex query and I cannot do a fast refresh. I am  not sure what is the impact of the complete refresh to the application. What happen when the view is being access while the refresh is happening? the table is not very big so I expecting just few sec or min to refresh. the db version is 9i
Re: Materialize view complete refresh [message #63521 is a reply to message #63515] Tue, 12 October 2004 17:20 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
There is no problem at all. When you do a complete refresh, Oracle just runs the SQL which defines your MV (just like you'd manually run the SQL query). You don't need the MV log tables on the source database to do a complete refresh.

-- 9am and 2pm
CREATE SNAPSHOT  scott.t1
TABLESPACE scott_data
REFRESH COMPLETE with rowid
START WITH sysdate
NEXT decode(SIGN (to_char(sysdate, 'hh24')-12), 
      1, (trunc(sysdate+1)+9/24), trunc(sysdate)+14/24)
AS
select * from t1@dev_db;
Re: Materialize view complete refresh [message #216965 is a reply to message #63521] Wed, 31 January 2007 01:38 Go to previous message
rawat_me
Messages: 45
Registered: September 2005
Member
But can i use REFRESH FAST on COMMIT option with your command:

-- 9am and 2pm
CREATE SNAPSHOT scott.t1
TABLESPACE scott_data
REFRESH COMPLETE with rowid
START WITH sysdate
NEXT decode(SIGN (to_char(sysdate, 'hh24')-12),
1, (trunc(sysdate+1)+9/24), trunc(sysdate)+14/24)
AS
select * from t1@dev_db;
Previous Topic: Materialized views
Next Topic: Materialized view with CLOB/BLOB
Goto Forum:
  


Current Time: Thu Mar 28 08:52:31 CDT 2024