Asynchronous materialized view FAQ

Build and refresh

Q1: How does a materialized view determine which partitions to refresh?

SelectDB maps partitions between a materialized view and its base tables, recording each base table partition version from the last successful refresh. On the next refresh, it checks for version changes in those partitions. A change triggers a refresh for the corresponding materialized view partition.

If your business logic allows ignoring changes in a non-partitioned table, you can add the table to the materialized view's excluded_trigger_tables property.

Q2: What can I do if a materialized view consumes too many resources and affects other services?

You can limit the refresh task's resource usage by specifying a workload_group in the materialized view's properties. If the memory limit is too low for a single partition refresh, the task will fail. Balance the resource limits with your business requirements.

Q3: Can I create a new materialized view based on an existing one?

Yes. SelectDB supports nesting materialized views. However, each view has an independent refresh logic. For example, if mv2 is created based on mv1, refreshing mv2 does not consider whether mv1 is synchronized with its base tables.

Q4: Which external data sources support creating materialized views?

You can create materialized views from any external table that SelectDB supports. However, only Hive supports incremental partition refreshes. Other external table types, such as Iceberg and JDBC, require a full refresh.

Q5: Why does my materialized view appear consistent with Hive data but is actually inconsistent?

A materialized view only guarantees consistency with the data as presented by the catalog. Because the catalog may cache metadata and data, synchronize the catalog with the source Hive data first. Use REFRESH CATALOG to update the catalog, and then refresh the materialized view.

Q6: Do materialized views support schema changes?

No. You cannot directly modify the column definitions of a materialized view because its columns are derived from the defining SQL. To make changes, recreate the materialized view.

Q7: Can the base tables of a materialized view undergo a schema change?

Yes. However, when a base table is altered, any materialized view built on it changes state from NORMAL to SCHEMA_CHANGE. In this state, the view cannot be used for transparent rewriting, although you can still query it directly.

Q8: Can I create a materialized view from a table that uses the primary key model?

Yes. You can create a materialized view from base tables of any data model, but the materialized view itself must use the duplicate key model.

Q9: Can I create an index on a materialized view?

Yes.

Q10: Does a materialized view refresh lock its base tables?

The refresh acquires a brief table lock but does not hold it for the entire refresh duration. The lock time is nearly identical to that of a data import and does not affect normal queries.

Q11: Are asynchronous materialized views suitable for near real-time scenarios?

Not recommended. A materialized view's smallest refresh unit is a partition. For large datasets, refreshing can be resource-intensive and introduce significant latency. For near real-time scenarios, consider synchronous materialized views or other real-time update methods.

Partitioning issues

Q1: How do I resolve the "Unable to find a suitable base table for partitioning" error when creating a partitioned materialized view?

This error occurs when the materialized view's SQL definition and partition column selection do not meet the requirements for incremental partition refreshes. To support incremental partition refreshes, a materialized view must meet these conditions:

• The materialized view's partition column must be derived from a partition column of a base table, either directly or by applying the date_trunc function to it.

• The partition column cannot be derived from a non-partition column on the nullable side of a LEFT JOIN.

For example, in the following statement, using orders.o_orderdate as the partition column is correct. Using lineitem.l_shipdate (a non-partition column on the nullable side of the LEFT JOIN) would cause an error.

-- Correct: Use the partition column o_orderdate from the orders table as the materialized view's partition column.
CREATE MATERIALIZED VIEW mv_correct
BUILD IMMEDIATE REFRESH AUTO ON MANUAL
PARTITION BY (o_orderdate)
DISTRIBUTED BY RANDOM BUCKETS 2
AS SELECT l_linestatus, sum(l_extendedprice * (1 - l_discount)) AS revenue, o_orderdate
   FROM orders LEFT JOIN lineitem ON l_orderkey = o_orderkey
   GROUP BY l_linestatus, o_orderdate;

Q2: How do I resolve the "BUILD IMMEDIATE REFRESH AUTO ON MANUAL Syntax error" when creating a materialized view?

This syntax is only supported by the new optimizer (Nereids). Ensure that the new optimizer is enabled:

SET enable_nereids_planner = true;

Q3: Why is my materialized view empty after a successful refresh?

Possible cause: A data source that does not support retrieving data version information, such as a JDBC catalog, causes the refresh process to assume no update is required. Solution: Specify a COMPLETE full refresh.

REFRESH MATERIALIZED VIEW mv_name COMPLETE;

Q4: Why does my partitioned materialized view always perform a full refresh instead of an incremental one?

This can happen when data changes in a non-partitioned base table that the materialized view depends on. The system cannot determine which partitions need an incremental update, so it falls back to a full refresh.

To resolve this:

• If changes in the non-partitioned table have little impact on the materialized view, you can exclude that table from triggering refreshes by setting the excluded_trigger_tables property.

• You can check the partition tracking details for the materialized view with the following SQL query:

  SELECT * FROM mv_infos('database'='db_name') WHERE Name = 'partition_mv' \G

Query and transparent rewriting

Q1: How can I confirm if a query was successfully rewritten to use a materialized view?

Use the EXPLAIN command to view the query plan. Look for these markers in the output:

• MaterializedViewRewriteSuccessAndChose: The rewrite succeeded and the optimizer chose this materialized view (a successful hit).

• MaterializedViewRewriteSuccessButNotChose: The rewrite succeeded, but the optimizer found a more optimal execution plan.

• MaterializedViewRewriteFail: The rewrite failed. This is followed by a summary of the failure reason.

Q2: Why is my query not being rewritten to use a materialized view?

Common reasons include:

• Features not enabled: Verify that both enable_materialized_view_rewrite and enable_nereids_planner are set to true.

• Materialized view is unavailable: The materialized view's state is not NORMAL (it might be SCHEMA_CHANGE or in a failed refresh state). Check its status by running SELECT * FROM mv_infos('database'='db_name') WHERE Name = 'mv_name'.

• SQL pattern mismatch: The SELECT, JOIN, GROUP BY, or WHERE pattern in the query is incompatible with the materialized view definition. For example, transparent rewriting is not supported for queries containing constructs like window functions, UNION ALL, or CROSS JOIN.

• Rewrite for external tables is disabled: If the materialized view is built on an external table, you must enable rewriting by setting SET materialized_view_rewrite_enable_contain_external_table = true.

Q3: What causes a materialized view's state to change from NORMAL?

Common reasons include:

• Base table data has changed: The data in a base table was changed by an import, update, or delete operation, making the materialized view's data stale.

• Base table schema has changed: The column definitions of a base table were altered, which changes the materialized view's state to SCHEMA_CHANGE.

• Refresh task failed: The last refresh task did not complete successfully, so the materialized view data was not updated.

In all these cases, the state automatically returns to NORMAL after the next successful refresh.

Q4: Why does a direct query on my materialized view return no data?

This can happen if the materialized view was created with BUILD DEFERRED (deferred refresh), or if the last refresh failed. Check the task status:

-- Check the status of refresh tasks
SELECT * FROM tasks('type'='mv') WHERE MvName = 'mv_name' ORDER BY CreateTime DESC LIMIT 1;

If the materialized view is empty, trigger a refresh manually: REFRESH MATERIALIZED VIEW mv_name COMPLETE;

Q5: If a base table changes before the materialized view is refreshed, will a rewritten query return stale data?

The behavior depends on the grace_period property and the enable_materialized_view_union_rewrite setting:

• If a grace_period is set, the materialized view can still be used for transparent rewriting within that staleness window. In this case, queries may return stale data.

• If grace_period is 0 (the default), the materialized view becomes ineligible for transparent rewriting as soon as a base table changes.

• For a partitioned materialized view, if enable_materialized_view_union_rewrite = true (the default), a query can combine data from the materialized view's up-to-date partitions with fresh data from the base table's changed partitions to ensure correct results.