Projects:Support Materialized Views In DBSourceManager/Specifications
Contents |
Introduction
This document will describe how materialized views are defined both in PostgreSQL and in Oracle. It will also describe the proposed default definition of the materialized views created by dbsourcemanager.
Materialized view definition
Here it is the documentation of materialized view definition for PostgreSQL and Oracle:
PostgreSQL: https://www.postgresql.org/docs/11/sql-creatematerializedview.html
Oracle: https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6002.htm
Next sections will cover a summary of the relevant parts of this documentation.
Population of the materialized view on creation
Both PostgreSQL and Oracle allow to choose if the materialized table should be populated when it is created.
To populate the data on creation, the BUILD IMMEDIATE clause must be used in Oracle, and the WITH DATA clause must be used in PostgreSQL.
To create the materialized view without doing an initial data population, the BUILD DEFERRED clause must be used in Oracle, and the WITH NO DATA clause must be used in PostgreSQL:
If a query is done to a materialized view that has not been initially populated and that has not been refreshed yet, it will return an empty resultset in Oracle, and it will return an error in PostgreSQL.
Full refresh vs incremental refresh
PostgreSQL does not support incremental refresh yet. Each time a materialized view is refreshed, it will completely replace its contents.
Oracle does support both full and incremental refresh of materialized views. To make a full refresh, the materialized view must be created with the REFRESH COMPLETE clause.
Incremental refresh can be achieved by creating the materialized view with the REFRESH FAST clause. Those materialized views will be refreshed according to the changes that have occurred in its referenced tables. Incremental refresh is not available for all materialized views, it depends on the materialized view query.
When to refresh the data of a materialized view
PostgreSQL
PostgreSQL offer very little functionality regarding the data refresh of materialized views. They must be manually refreshed using the
REFRESH MATERIALIZED VIEW <materializedViewName>
If the materialized view was created with the WITH NO DATA clause, it will not be populated on creation and it will not be readable until it is refreshed.
Oracle
Oracle offers much more functionality regarding the data refresh of materialized views.
Like PostgreSQL, it allows to determine a materialized view should be refreshed manually by using the ON DEMAND clause.
Oracle also allows to automatically refresh a materialized view each data data in any of its referenced tables is commited by using the ON COMMIT clause. This makes commits on those tables slower, since the refresh of the materialized view is now part of the commit process.
Automatic periodic data refresh is also possible using the REFRESH ... [START ...] NEXT ... clause. The optional START subclause determines when the materialized view will be refreshed for the first time, and the NEXT clause determines when data will be refreshed from them on.
For instance, REFRESH COMPLETE NEXT sysdate + 7 would refresh the data every 7 days.
REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 NEXT NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 15/24 would refresh the data for the first time the day after the the materialized view is created at 11am, and every next mondays at 3pm.
It is possible to specify that a materialized view should never be refreshed by using the NEVER REFRESH clause.
Materialized view support in DBSourcemanager
These are the proposed approach to the materialized views configurable functionality:
Population of the materialized view on creation
Materialized views will not be populated on creation.
Full refresh vs incremental refresh
Materialized views will be fully refreshed. Not only that's the only method available in PostgreSQL, but in Oracle incremental refreshed is not supported for all materialized views.
When to refresh the data of a materialized view
Materialized views will be loaded on demand. It is the only method available in PostgreSQL. In Oracle it would be possible to refresh them on commit (not a good option since it would slow down the commits done on referenced tables) or periodically (not suitable either, since other way of refreshing data periodically will be needed for PostgreSQL anyway.
Examples
This is how materialized views will be created according to the chosen default functionality:
PostgreSQL
CREATE MATERIALIZED VIEW c_order_mv AS SELECT c_order_id, c_bpartner_id, dateordered FROM c_order WHERE processed = 'Y' WITH NO DATA;
Oracle
CREATE MATERIALIZED VIEW c_order_mv BUILD DEFERRED REFRESH COMPLETE ON DEMAND AS SELECT c_order_id, c_bpartner_id, dateordered FROM c_order WHERE processed = 'Y';