Full Refresh

Full Refresh Principles

Full refresh of a materialized view uses the INSERT OVERWRITE logic to fully recalculate the SQL statements of the materialized view and materialize the result set.

Full refresh syntax:

1
2
3
4
5

CREATE MATERIALIZED VIEW test_mv1
REFRESH COMPLETE EVERY (interval '01:00:00') -- Trigger a refresh every hour.
ENABLE QUERY REWRITE -- Enable query rewriting.
WITH(orientation=COLUMN, enable_hstore_opt=ON) -- Use the column-store HStore Opt format.
AS Your_Select;

Constraints

The entire SQL statement is re-executed. Therefore, there is no special requirements, and this method is widely used. Base tables can be internal tables, foreign tables, or other materialized views. Base tables can be stored in rows or columns.

There is no special requirement for the storage format of materialized views.

Impact on Services

• During the refresh, all data is queried and calculated from a base table and written to a temporary table. Then, indexes and statistics are maintained. An exclusive lock needs to be added to the materialized view only when the temporary table exchanges data files with the materialized view, blocking the query within milliseconds.
• If a complex and time-consuming SQL statement is executed on the materialized view during the refresh, the file exchange during the full refresh will be blocked. Setting INSERT_OVERWRITE in ddl_select_concurrent_mode can kill the query and then the refresh can be completed quickly. The killed query can be automatically retried.

Scenarios

Full refresh is ideal for batch update scenarios where historical data changes frequently. Generally, the refresh frequency is at the hour level. The specific situation depends on the actual service complexity and resource conditions.