How Materialized View Based Optimizer (MBO) Supercharges Inceptor Queries

What is MBO?

Materialized View Based Optimizer (MBO) is an optimizer that, for a given SQL statement, leverages existing materialized views to generate the best execution plan, allowing the query to directly read the view result and then apply any additional business‑logic processing.

Core Function of MBO

After optimization, MBO reduces the data set to the materialized view, achieving faster query execution.

Materialized View Types in Inceptor

Original materialized view created via dedicated DDL.

Cube instance created by Transwarp Rubik; Inceptor obtains the cube table name and DDL from Studio.

Configuration Parameters

inceptor.mbo.enable

– global switch, default false. inceptor.mbo.for.naive.mv – whether original materialized views can be used as MBO sources, default false. inceptor.mbo.for.cube – whether Cube tables can be used as MBO sources, default false. If Studio is unavailable, the setting has no effect.

Supported Scenarios

Aggregation

MBO supports two aggregation matching patterns.

1. Exact GROUP BY match – the query and view have identical aggregation columns.

CREATE MATERIALIZED VIEW mv ENABLE REWRITE AS
SELECT c1, c2, SUM(v1) AS sv1, COUNT(v1) AS cv1
FROM ttt GROUP BY c1, c2;

SELECT c1, c2, AVG(v1) FROM ttt GROUP BY c1, c2;

The optimized plan reads mv and adds the necessary SELECT part, equivalent to: SELECT c1, c2, sv1/cv1 FROM mv; 2. Extra aggregation layer – the view aggregates at a finer granularity; MBO adds an extra aggregation step.

CREATE MATERIALIZED VIEW mv ENABLE REWRITE AS
SELECT c1, c2,
SUM(v1) AS sv1,
COUNT(v1) AS cv1,
MAX(v1) AS maxv1,
MIN(v1) AS minv1,
AVG(v1) AS avgv1
FROM ttt GROUP BY c1, c2;

SELECT c1, SUM(v1), COUNT(v1), MAX(v1), MIN(v1), AVG(v1)
FROM ttt GROUP BY c1;

The optimized plan adds a GROUP BY on c1 and aggregates the fine‑grained results:

SELECT c1,
SUM(sv1), SUM(cv1), MAX(maxv1), MIN(minv1), SUM(sv1)/SUM(cv1)
FROM mv GROUP BY c1;

Filter

If both the view and the query contain filters and the query filter is a subset of the view filter, MBO can shrink the filter condition.

CREATE MATERIALIZED VIEW mv ENABLE REWRITE AS
SELECT * FROM a WHERE c1 > 10;

SELECT * FROM a WHERE c1 > 15;

Optimized plan reads from mv with the tighter filter:

SELECT * FROM mv WHERE c1 > 15;

Join

MBO supports full join match and partial match within INNER JOIN.

1. Full match – same tables, join type, and join condition.

CREATE MATERIALIZED VIEW mv ENABLE REWRITE AS
SELECT a.v1, a.id2
FROM a JOIN b ON a.id = b.id
JOIN c ON a.id1 = c.id;

SELECT a.v1, a.id2
FROM a JOIN b ON a.id = b.id
JOIN c ON a.id1 = c.id;

Optimized plan reads directly from mv: SELECT v1, id2 FROM mv; 2. Partial match in INNER JOIN – the view’s INNER JOIN is a subset of the query’s INNER JOIN.

CREATE MATERIALIZED VIEW mv ENABLE REWRITE AS
SELECT a.v1, a.id2
FROM a JOIN b ON a.id = b.id
JOIN c ON a.id1 = c.id;

SELECT a.v1 FROM a
JOIN c ON a.id1 = c.id
JOIN b ON a.id = b.id
JOIN d ON a.id2 = d.id;

Optimized plan uses the view for the matching part:

SELECT mv.v1 FROM mv
JOIN d ON mv.id2 = d.id;

Partial Rewrite

If only a portion of the query matches a materialized view, MBO rewrites that portion.

CREATE MATERIALIZED VIEW mv ENABLE REWRITE AS
SELECT a.v1 FROM a JOIN b ON a.id = b.id;

SELECT a.v1 FROM a JOIN b ON a.id = b.id
UNION
SELECT a.v2 FROM a JOIN b ON a.id = b.id;

Optimized plan rewrites the matching part:

SELECT mv.v1 FROM mv
UNION
SELECT a.v2 FROM a JOIN b ON a.id = b.id;

Using Rubik and MBO for OLAP Workloads

With Transwarp Rubik, users create a Cube that pre‑aggregates the required dimensions and measures. After the Cube is instantiated, enabling MBO (and allowing Cube as a source) lets the optimizer automatically select the appropriate Cube table and generate the optimal plan, dramatically reducing query latency.

Conclusion

Materialized views pre‑aggregate and pre‑join data, eliminating repeated heavy computations. However, selecting the right view among many is difficult; MBO automates view selection and execution‑plan rewriting, fully exploiting the performance benefits of materialized views and improving the usability of analytical systems.