How to refresh materialized view?
Here is the syntax to refresh materialized view and update it with new data.
In PostgreSQL:
REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name
[ WITH [ NO ] DATA ]
;
By default the materialized view will be locked during refreshing, so if you want it to be accessed at the same time, use CONCURRENTLY.
If WITH DATA is specified (or defaults) the backing query is executed to provide the new data, and the materialized view is left in a scannable state. If WITH NO DATA is specified no new data is generated and the materialized view is left in an unscannable state.
In Oracle:
You can use the DBMS_MVIEW to refresh the materialized view:
EXECUTE DBMS_MVIEW.REFRESH('name', ['f' | 'c']);
Use f when you need fast refresh and c when you need a complete refresh.
You can also use DBMS_SNAPSHOT as a synonym of DBMS_MVIEW:
EXECUTE DBMS_SNAPSHOT.REFRESH('name', ['f' | 'c']);
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
