I have an mview which will be refreshed daily. But for the first time its going to be a complete refresh and the second time onwards it will be a specific refresh(say for 2 months) leaving the data older than 2months without refreshing.
It doesn't compare, you make a MVIEW LOG on the machine that is hosting the original table and it will automatically keep track of everything that changed. When you do the fast refresh, it reads the MVIEW LOG on the other system and pulls over only the rows that changed. If you do the refresh every day (or every 15 minutes for example), it will move over only the few records that it has to.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
I have a question on Indexes on Mviews. Usually we drop the indexes on tables before running an ETL on it and that way, it loads quickly. Is it the same with Mviews too? Do i have to drop the indexes before I refresh the mview? Does the indexes created by Oracle automatically help in any way in performance?
I looked in the manual and it said to create a log on the tables in the Mview if I'm using a fast refresh in the Mviews. I cant create a PK as the table contains NULL values. I created the Mview log using ROWID as follows.
create materialized view log on asset with rowid;
create materialized view log on book with rowid;
create materialized view log on cost with rowid;
create materialized view tab_mv
refresh fast with rowid
select col1, col2, sum(col3)
group by .....
I came across this article which says I cant refresh fast an Mview with rowid when I have aggregates and group by. Any suggestions.