Best Practices of Materialized View Design

Refresh Task Scheduling

• Preventing sudden CPU usage spikes

  Refresh tasks in off-peak hours to avoid centralized refresh. Use START WITH and EVERY to specify the refresh time and frequency. Set different refresh time for different MVs.

• Preventing plan changes

  Use hints to fix execution plans as much as possible to reduce the impact of plan changes on query performance.

• Preventing repeated refreshes

  Do not create a refresh task for each layer if materialized views are used for cascading and refresh the views in cascading mode based on the data flow pipeline. Use CASCADE BACKWARD.

• Ensuring that the refresh interval at the upper layer must be greater than the maximum interval at the lower layer

  Positive example:

  1. Upper-layer MV2: Refresh every 10 minutes (≥ 5)
  2. Lower-layer MV1: Refresh every 5 minutes

  Negative example: When the upper layer is refreshed, the old data at the lower layer is displayed.

  1. Upper-layer MV2: Refresh every 2 minutes (< 5)
  2. Lower-layer MV1: Refresh every 5 minutes

Storage Format Selection

The HStore Opt storage format is recommended.

1
2
3

CREATE MATERIALIZED VIEW mv_test
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS SELECT ...

Typical Application Modes

Mode 1: Report acceleration

• Business scenario: daily sales report
• Base table: sales details table (hundreds of millions of data records)
• Query: daily sales summary

Example:

Create a materialized view.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

CREATE MATERIALIZED VIEW mv_sales_daily
REFRESH FAST EVERY (interval '1 hour')
ENABLE QUERY REWRITE
WITH (orientation = COLUMN, enable_hstore_opt = ON)
AS
SELECT
DATE(sale_time) as sale_date,
region,
product_category,
SUM(amount) as total_amount,
COUNT(*) as order_count
FROM sales_detail
GROUP BY DATE(sale_time), region, product_category;

Mode 2: Layered hybrid data warehouse

1. DWD layer: detail cleansing

   1 2 3 4

   CREATE MATERIALIZED VIEW mv_dwd_sales REFRESH FAST EVERY (interval '5 min') ENABLE QUERY REWRITE AS SELECT * FROM source_sales WHERE status = 'valid';

2. DWS layer: basic aggregation

   1 2 3 4 5 6 7 8 9 10 11

   CREATE MATERIALIZED VIEW mv_dws_sales REFRESH FAST EVERY (interval '10 min') ENABLE QUERY REWRITE AS SELECT sale_date, region, SUM(total_amount) as amount, SUM(order_count) as orders FROM mv_dwd_sales GROUP BY sale_date, region;

3. ADS layer: report application

   1 2 3 4 5 6 7 8 9 10 11

   CREATE MATERIALIZED VIEW mv_ads_sales REFRESH FAST EVERY (interval '30 min') ENABLE QUERY REWRITE AS SELECT sale_date, region, amount, orders, amount / orders as avg_order_value FROM mv_dws_sales;