Home » RDBMS Server » Performance Tuning » Usage of Materialized view to improve performance
Usage of Materialized view to improve performance [message #310559] Tue, 01 April 2008 11:37 Go to next message
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 Go to previous messageGo to next message
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

Re: Usage of Materialized view to improve performance [message #310792 is a reply to message #310559] Wed, 02 April 2008 07:36 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Here are the detailed steps how we overcome this situation for searches:
Effective search in a normalized application

Gints Plivna

[Updated on: Wed, 02 April 2008 07:44] by Moderator

Report message to a moderator

Previous Topic: How to re-write th eprocedure...?
Next Topic: Find a correct value for PGA_AGGREGATE_TARGET
Goto Forum:
  


Current Time: Sun Nov 03 06:40:17 CST 2024