What is the difference between complete refresh and fast refresh in materialized view?
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
"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.