I have 3 reporting tables with 2.2 million records each being rebuilt nightly. The data is used online 24/7 by users and thus, snapshot tables are being built from the refreshed reporting tables. The current method to do this:
delete from snapshot table;
insert into snapshot table (select * from report table);
<repeat for other 2 tables>
This seems to me to be resource intense on the system even though the table is defined with nologging option.
Is it better to create a MV (select only with refresh complete on demand)? The query is very simple without joins so it at first seems like overkill. However, I am also seeing that dbms_mview.refresh allows for an atomic option. Thus, if 1 of the 3 MVs fails during refresh all 3 rollback, which is a nice feature.
Are there better ways to replicate a snapshot table that I've missed? Is a delete and insert strategy a bad idea?
If the query is simple, would it be possible to let the materialized view do an incremental refresh instead (this would require that a materialized view log be created on the base table(s))? If so, that would definitely make refreshing the materialized view more efficient.
Would you gain any benefit from enabling query rewrite in the database so that queries against the base table could be rewritten to use the materialized view? Ideally, you can use materialized views in this way to optimize many different queries against the base tables and simplify the process of building new queries by letting people query the base table and then letting the optimizer figure out if the materialized view can be used to improve performance. That lets you treat adding new materialized views much like you would treat adding new indexes-- a way to transparently increase the performance of a number of different queries without touching the queries themselves.