Materialized Views for Reporting

Reporting is one of the important issues in AR System environment. If you are not able to take proper reports from your system, it is not meaningful to enter data, is not it? While you are writing select statements or even for updates, you can use ARS views e.g. select * from ar_system_email_messages. However you should remember that some workflow is running on this ARS form. When you are selecting from ARS view, you are accessing to the T table and you may lock the table. This actually would cause the workflow to run into errors.

For reporting, I recommend you to build a single interface although you may have more than one consumers. At least you can try to decrease the number of requested views as much as possible to avoid unnecessary DB load. To me the best structure for reporting is Materialized Views. Although its name is "view", it behaves like a table e.g. you can define index and triggers on it. It keeps all data on a separate place, therefore you do access to the ARS tables everytime you query.

Now let's come to the point how we populate data to Materialized Views. The name of the operation modifying a materialized view is called "Refresh". In Oracle, you can right click to the materialized view and you can see the "Refresh" option. There are different modes of refresh. If your view is basic and does not contain functional statements and complex joins, you can define Fast refresh. This option tracks the changes on the base tables of the view and refreshes the view considering these changes. It does not touch the unmodified data and that's why it is fast. There is also "Complete" mode. This builds the view from the beginning. You should know that there will be a huge data transfer with this mode. Here there is another concept called "Atomic" and "Non-atomic" refresh. If you select "Atomic" refresh, Oracle first detects the modified rows and changes these rows in the original view by inserts, updates and deletes. This is the reason why it is much slower compared to "Non-atomic" refresh. In our environment, we had a huge and complex view. For this view, "Complete Non-atomic" is the best refresh combination for materialized view. There is also another refresh mode called "Force". If this mode is selected, first Fast mode is taken to consideration. If it is not possible, a Complete refresh is done. Please note that refresh modes (Fast/Complete/Force) is defined while creating the view and refresh types (Atomic/Non-atomic) can be given in run time.

According to your need, you can build different materialized views e.g. you can keep last 7 days data in one view and last 6 months data in another view. In order to automate refreshes, you can define a stored procedure and call this by a scheduled job. Here is the piece of code which you can use for refresh in stored procedure:

DBMS_MVIEW.REFRESH('VIEW_NAME', ATOMIC_REFRESH=>FALSE);