Hi Friends!!!
Greetings for the day.
I have created materialzed views to get the snapshot of remote database tables on oracle 11g and have written procedures to refresh the materialzed view. I have scheduled a job to refresh the materialized views over every weekend. Right now i am doing complete refresh every week-end and for one of the MV, it is taking approximately 10hrs to get refresh because of massive data approximately that contains 12752543 rows and that will keep grow. Now my team is asking for daily refresh. Now i was given the task to refresh all the MV's on daily basis. I have done some research on minimizing refresh time. there I got to know that fast refresh is the ideal way to go. But i am not very clear with concept, how incremental refresh would work. So may I request any one of you to help me in understand the concept with one example.
My Data table structure would look like below on remote database and every row in this table is unique row. Remote database table gets refreshed once in a day. I want to have snapshot of that remote database table onto my schema and it should be refresh every day.
on 12/4/2014 assume that i have below data on remote database for issue_id-1
| Issue_id | issue_name | issue_create_date | issue_modify_date | review_status | issue_status | data_extract_date |
| 1 | ABC | 12/1/2014 | 12/4/2014 | site reiview | open | 12/4/2014 |
on 12/5/2014 assume that the data has been changed for issue_id-1
| Issue_id | issue_name | issue_create_date | issue_modify_date | review_status | issue_status | data_extract_date |
| 1 | ABC | 12/1/2014 | 12/5/2014 | DM Review | Closed | 12/5/2014 |
When i am doing incremental refresh how the old data of issue-1 will replace with modified data of issue-1 and how the table is updated with other new rows. Please let me understand this scenario.
thank you very much in advance.