What is the difference between complete refresh and fast refresh in materialized view?

Question

I have materialized view in my own schema and the materialized view source is the other schema table but , this master table has 900 rows and i did some dml operations on this master table after that i will refresh this materialized view as you know , and i did some resarch " how can i refresh my own materialized view " and it says " you can do complete refresh or fast refresh " but i didnt understand these solutions meaning so my question is simple ;

What is the difference between complete refresh and fast refresh in MV ?

P.S:If my master table has 1 million or more rows , which one i should choose? (fast or complete)

Thank you for your reply.

Answer 1

"Complete Refresh" means you truncate entire materialized view and insert new data.

"Fast Refresh" means you update (or insert/delete) only the rows which have been changed on master tables.

And just as information "Force Refresh" mean, Oracle tries to make a Fast Refresh and if this is not possible then do "Complete Refresh"

Usually Fast Refresh is much faster than Complete Refresh but it has restrictions. You have to define MATERIALIZED VIEW LOG on master tables.

Here is a full list of restrictions General Restrictions on Fast Refresh, there are quite many.

Answer 2

As always it depends, so if possible try both and measure for your application. As a general rule the fast refresh is likely to be much faster provided that only a small part of the data has changed. If all the data has changed a complete refresh is better.

With the fast refresh Oracle keeps track of the changes for the base tables and applies the changes to the materialized view when it is refreshed. A complete refresh on the other hand rebuilds the materialized view from scratch. With millions of rows that will be expensive, but again it is impossible to pick the best option without knowing more about your application.