| Materialize view complete refresh [message #63515] |
Tue, 12 October 2004 05:23  |
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  |
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 |
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;
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
