Materialized Views on Oracle
This section describes the steps you need to follow to work with Materialized Views on Oracle.
A materialized view on Oracle is a database view that is stored physically and provides a view on which indexes and keys can be defined. BPM Reporting uses one materialized view in the Job Information universe because the Maconomy transaction database lacks a table that combines job entries (JobEntry) with corresponding invoice lines (JobInvoiceLine).
This section describes how materialized views on Oracle work and how to manage them.
JobEntryJobInvoiceLinePV
The materialized view that is used in BPM Reporting is called JobEntryJobInvoiceLinePV. It consists of the following elements:
- Two view logs, one on JobEntry and one on JobInvoiceLine
- Materialized view JobEntryJobInvoiceLinePV
- EX view EXJobEntryJobInvoiceLinePV
When records change in the JobEntry table, the changes are recorded in the view log. Similarly, if records are inserted or deleted, changes are recorded in the JobInvoiceLine table.
The Materialized View
The materialized view is set to update on commit. This means that when a transaction changes, inserts, or deletes records in JobEntry or JobInvoiceLine, the changes that are recorded in the view logs are transferred as changes that update the materialized view. The view is designed to perform a fast refresh because only delta changes are considered; it is not the whole view that is updated. In addition, the view does not use the query rewrite mechanism in Oracle.
The EX View
The EX view is a database view that is on top of the materialized view. The EX view provides meaningful values for pop-up numbers (enumerated data types) and date values of data types (in addition to the string format).
For technical reasons, the date definitions are done in the materialized views, and only pop-up value fields are handled in the EX views (in other EX views, date fields are also handled in the EX views).
Explain the View Definition
This section provides the view definition file and explains what each statement does.
| Statement/Section | Definition |
|---|---|
| ALTER SYSTEM SET "_mv_refresh_use_stats =TRUE" | This statement tells Oracle to use statistics when refreshing materialized views. |
| CREATE MATERIALIZED VIEW LOG ON JOBENTRY | This statement creates the view log on JobEntry. |
| WITH ROWID | The reference of ROWID is important because it is used for unique identification. |
| INCLUDING NEW VALUES; CREATE MATERIALIZED VIEW JOBENTRYJOBINVOICELINEPV | This statement creates the actual materialized view. |
| REFRESH ON COMMIT AS | This is the definition of "refresh on commit." |
| SELECT | This statement starts the select statement that defines the view. |
| -- Technical fields JOBENTRY.ROWID JOBENTRYROWID | Both parts of the union that make the select statement use the ROWID internally for unique identification. |
| ,NULL JOBINVOICELINEROWID ,1 MARKER | This field is used to distinguish the upper and lower union parts. |
| -- Shared Dimensions ,JOBENTRY.INSTANCEKEY | This starts the field list. |
| CREATE VIEW EXJOBENTRYJOBINVOICELINEPV AS : SELECT INSTANCEKEY ,JOBNUMBER ,ENTRYNUMBER | This defines the EX view on the materialized view. |
Check the Materialized View
Use the following select statements to check the view.
SELECT MVIEW_NAME, REFRESH_MODE, REFRESH_METHOD, LAST_REFRESH_TYPE, LAST_REFRESH_DATE FROM USER_MVIEWS;This lists a record for the materialized view that is defined. The last_refresh_date provides the date when the view was last refreshed, and the other fields indicate how it was refreshed.
SELECT LOG_OWNER, MASTER, LOG_TABLE FROM DBA_MVIEW_LOGS;This lists two records, one for each view log.
SELECT COUNT(*) FROM MLOG$_JOBENTRY; SELECT COUNT(*) FROM MLOG$_JOBINVOICELINE;These statements display the number of changes currently recorded in the view logs. Under normal circumstances, they should display zero. If they do not, it is because they currently store changes that are not transferred to the materialized view.
If it appears that the preceding tables are not emptied at any time, it can be because the materialized view is not able to empty them as it should. One reason can be that more than the two view logs have been installed, but without the corresponding materialized views to empty them.
To check that _mv_refresh_use_stats system parameter is set up, use the following command:
SHOW PARAMETER _mv_refresh_use_stats;
It should display the following.
| NAME | TYPE | VALUE |
| _mv_refresh_use_stats | boolean | TRUE |
If no data is displayed, the parameter is not set up.
Flush the Shared Pool
Over time, SQL statements for updating the materialized view compile in the shared pool. This is a known issue in Oracle and requires that you empty the shared pool. A stored procedure has been developed for flushing the shared pool every night.
To check that the flushing procedure runs as expected, execute the following command. FAILED should be zero and BROKEN should be "N."
select job, what, last_date, next_date, failures, broken from dba_jobs where what like '%maconomy_flush_shared_pool%';
To check that the flushing procedure cleaned the shared pool, execute the following command. If it is successful, it should return no entries.
SELECT sql_text FROM v$sql WHERE sql_text LIKE '/* MV_REFRESH%';
Handling Issues
If the Maconomy database performs poorly, it may be because of an incorrect installation of the materialized view.
To resolve this issue, follow these steps:
- Run the select statements mentioned in Check the Materialized View. Check the results to find out whether the view is updated at all, and whether it is refreshed quickly.
- Run the select statements mentioned in Flush the Shared Pool to find out whether the stored procedure is not flushing correctly and how many entries there are in the shared pool.
- Drop the view. Use the drop statements of the view definition file. Start with dropping the actual materialized view and then drop the view logs. There is no need to drop the indexes.
- Apply the view again and check its installation.
- Install the flushing mechanism again and check its installation.