Creating Materialized View For Annual Report Based On Slow Function

Consider the following scenario: I have a table with 1 million product ids products : create table products ( pid number, p_description varchar2(200) ) also there is a relatively

Solution 1:

First, you do not have a "group by" query, so you can remove that.

An MV would be most useful if you needed to recompute all of the data for all years. As this appears to be a summary, with no need to reprocess old data, updated only when certain threshold dates like end of year are passed, I would recommend putting the results in a normal table and only adding the updates as often as your threshold dates occur (annually?) using a stored procedure. Otherwise your MV will take longer to run and require more system resources with every execution that adds a new date.

Solution 2:

Do not create a materialized view. This is not just a performance issue. It is also an archiving issue: You don't want to run the risk that historical results could change.

My advice is to create a single table with a "year" column. Run the query once per year and insert the rows into the new table. This is an archive of the results.

Note: If you want to recalculate previous years because the results may have changed (say the data is updated somehow), then you should store those results in a separate table and decide which version is the "right" version. You may find that you want an archive table with both the "as-of" date and the "run-date" to see how results might be changing.