Home » RDBMS Server » Performance Tuning » Materialized view refresh methods
Materialized view refresh methods [message #214737] Wed, 17 January 2007 18:53 Go to next message
Ksaravan
Messages: 17
Registered: January 2007
Location: Portland
Junior Member
Hi,

Can you please tell me when to use what kind of refresh while creating Materialized view on datawarehouse enviornment. environment is the best practice followed in industry. What is force on demand refresh ?

Regards
KSARAV
Re: Materialized view refresh methods [message #214743 is a reply to message #214737] Wed, 17 January 2007 19:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look here and here.

Ross Leishman
Re: Materialized view refresh methods [message #215527 is a reply to message #214737] Mon, 22 January 2007 13:13 Go to previous messageGo to next message
Ksaravan
Messages: 17
Registered: January 2007
Location: Portland
Junior Member
hi, thanks for your response. still i didnt get what needs to be used where? its talking about all types of refresh and their limitations? But what is best practice, in DW environment currenly just we are following complete refresh. im not sure whether it right method to use or not? if some complecated mviews if it didnt work with complete i will add with row id option it will working.

Regards
Ksaravan
Re: Materialized view refresh methods [message #215544 is a reply to message #215527] Mon, 22 January 2007 18:05 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Best practice is to use whichever is fastest.

If you update 5 rows in a million row base table, then fast-refresh will probably be best for dependent MVs.

If you update 500,000 rows in the same table, complete refresh will probably be faster.

Do some benchmarking. There will be a break-even point that will depend on the SQL in your MV. Fast Refresh deletes and replaces rows, so the break-even point is usually pretty low.

Ross Leishman
Previous Topic: Veiw synonym performs differently in diferent users
Next Topic: Tuning in 10g
Goto Forum:
  


Current Time: Wed Dec 07 06:44:13 CST 2016

Total time taken to generate the page: 0.07015 seconds