i have a big table (600 milions of records) and a background process that load data in this table every night (never update, only insert or delete).
The loading process is not a problem now, but i don't want to exceed whit indexes to avoid overloading on load process.
In order to improve the response time of report on the front end i introduced a Materialized view with refresh on demand (because users of front end cannot modify the data, but only require some report ) that joins the master big table with others two table.
The problem is that complete refresh process is too expensive!
1) Is my solution the better one?
2) Refresh on demand is the better solution?
3) Master table is partitioned, could be a good idea to partition also my materialized view?