View source | View content page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

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';

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Support_Materialized_Views_In_DBSourceManager/Specifications"

This page has been accessed 897 times. This page was last modified on 17 October 2019, at 08:50. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.