| Usage of Materialized view to improve performance [message #310559] |
Tue, 01 April 2008 11:37  |
sandhyaa
Messages: 79
Registered: February 2007
|
Member |
|
|
Hi,
I am using Materialized view to improve the query performance and defined its refresh time as every 3 hours.
The materialized view uses around 5 mins for refreshing. The problem is that during this 5 mins of refresh, the materialized view data is unavailable.
What can be done to avoid this?
Regards,
Sandi
|
|
|
|
| Re: Usage of Materialized view to improve performance [message #310560 is a reply to message #310559] |
Tue, 01 April 2008 11:41  |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
use the atomic_refresh option.
This will refresh the materialised view in a single transaction, so the rows should just "appear" when the transaction is finished.
However, it does a "delete" to maintain transaction integrity. THis can slow down the refresh.
Another alternative is to refresh a separate mview and "partition exchange" into the real one after refresh. You need exclusive lock on the table, but it is instantanous once you have lock.
EDIT:
Actually, not sure that last option will work on mviews, but you could try.
[Updated on: Tue, 01 April 2008 11:42] Report message to a moderator |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
