You can use query syntax to reference materialized views, such as SELECT, INSERT INTO SELECT, and WITH.

Required permissions

  • You must have SELECT permissions on materialized views.
  • You do not need to have the base table permissions involved in materialized views.

You can use GRANT to grant SELECT permissions on materialized views to users.

GRANT SELECT ON test.mymv TO 'user'@'%'

Automatic rewrite

Note This feature is in the experimental phase.

If query rewrite is enabled for materialized views, queries are automatically rewritten to authorized materialized views. You can use materialized views as custom caches. You do not need to modify your SQL statements.

If you want to use this feature, you must enable QUERY REWRITE on views and add a hint to specify this feature on the query.

/*+MV_QUERY_REWRITE_ENABLED=true*/
SELECT ...

You can use the EXPLAIN syntax to check whether rewrites take effect.

# Create a materialized view.
CREATE MATERIALIZED VIEW mv_max_cost
AS
SELECT max(cost) as max_cost FROM orders

# Test.
EXPLAIN SELECT max(cost) as max_cost FROM orders
# The EXPLAIN statement shows that the results stored in mv_max_cost are queried,
instead of querying the recalculation of orders.

If the automatic rewrite feature does not take effect, check the following factors:

  • Whether materialized views have limits.
  • Whether you have SELECT permissions on the materialized views.
  • Whether materialized views expire or are being refreshed.

Limits

If materialized views contain the following content, the materialized views are not automatically rewritten:
  • SELF JOIN (The same table is repeated.)
  • ORDER BY, LIMIT, or Offset clause
  • UNION or UNION ALL clause
  • GROUPING SETS, CUBE or ROLLUP in the GROUP BY clause
  • HAVING clause
  • Window Functions
  • Full Outer Join
  • System tables
  • Subqueries in the WHERE clause
  • Non-deterministic functions, such as now, current_timestamp, and random, and user-defined functions
Following queries involved in statements to modify data are not rewritten:
  • CREATE TABLE AS SELECT
  • INSERT INTO SELECT
  • INSERT OVERWRITE SELECT
  • REPLACE INTO SELECT
  • DELETE / UPDATE
Other situations

If a single-table query statement does not have filter conditions or aggregate functions, automatic rewrite does not apply to the query statement.

Rewrite range

AnalyticDB for MySQL extracts the structural information of query statements to match materialized views and rewrite query statements. Query statements do not need to be identical with materialized views. Query statements are rewritten only when they meet the condition of logical equivalence. AnalyticDB for MySQL attempts to rewrite queries and their subqueries of these queries. Different parts of a query may be rewritten by multiple materialized views.

AnalyticDB for MySQL can rewrite statements that contain JOIN, Filter, and Project operators. AnalyticDB for MySQL can also rewrite Aggregation and summarize the rewrite. If materialized views do not contain all results, materialized views attempt to rewrite the results by using the UNION operator.

JOIN, Aggregation, Aggregation Rollup, Query Partial, UNION, and Union with Aggregation can be used to rewrite queries.

The following examples show the automatic rewrite capability of AnalyticDB for MySQL. All examples are based on the following table schema:

CREATE TABLE depts(
  deptno INT NOT NULL,
  deptname VARCHAR(20),
  PRIMARY KEY (deptno)
);
CREATE TABLE locations(
  locationid INT NOT NULL,
  state CHAR(2),
  PRIMARY KEY (locationid)
);
CREATE TABLE emps(
  empid INT NOT NULL,
  deptno INT NOT NULL,
  locationid INT NOT NULL,
  empname VARCHAR(20) NOT NULL,
  salary DECIMAL (18, 2),
  PRIMARY KEY (empid),
  FOREIGN KEY (deptno) REFERENCES depts(deptno),
  FOREIGN KEY (locationid) REFERENCES locations(locationid)
);
Rewrite subqueries
Query
SELECT t1.cnt, t2.deptname from (
  SELECT deptno, COUNT(*) AS cnt
  FROM emps
  GROUP BY deptno) t1
JOIN depts t2 ON t1.deptno = t2.deptno
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT deptno, COUNT(*) AS cnt
FROM emps
GROUP BY deptno
Rewrite result
SELECT t1.cnt, t2.deptname
FROM MV t1
JOIN depts t2 ON t1.deptno = t2.deptno
Rewrite Join
Query
SELECT empid
FROM depts
JOIN (
  SELECT empid, deptno
  FROM emps
  WHERE empid = 1) AS subq
ON depts.deptno = subq.deptno
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid 
FROM emps
    JOIN depts on emps.deptno, depts.deptno
Rewrite result
SELECT empid
FROM mv
WHERE empid = 1
Rewrite Aggregation
Query
SELECT empid, SUM(salary) AS s
FROM emps
GROUP BY empid
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, SUM(salary) AS s, COUNT(*) AS c
FROM emps
GROUP BY empid
Rewrite result
SELECT empid, s
FROM mv
Rewrite Aggregation Rollup
Query
SELECT empid, COUNT(*) AS c, SUM(salary) AS s
FROM emps
GROUP BY empid
where deptno = 0
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptno, COUNT(*) AS c, SUM(salary) AS s
FROM emps
GROUP BY empid, deptno
Rewrite result
SELECT empid, SUM(c), SUM(s)
FROM mv
GROUP BY empid
where deptno = 0
Rewrite Query Partial
Query
SELECT deptname, state, SUM(salary) AS s
FROM emps
JOIN depts ON emps.deptno = depts.deptno
JOIN locations ON emps.locationid = locations.locationid
GROUP BY deptname, state
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptno, state, SUM(salary) AS s
FROM emps
JOIN locations ON emps.locationid = locations.locationid
GROUP BY empid, deptno, state
Rewrite result
SELECT deptname, state, SUM(s)
FROM mv
JOIN depts ON mv.deptno = depts.deptno
GROUP BY deptname, state
Rewrite UNION
Query
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 12000
Rewrite result
SELECT empid, deptname
FROM mv
UNION ALL
SELECT empid, deptname
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000 AND salary <= 12000
Rewrite Union with Aggregation
Query
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 10000
GROUP BY empid, deptname
Materialized view
CREATE MATERIALIZED VIEW
MV
ENABLE QUERY REWRITE
AS
SELECT empid, deptname, SUM(salary) AS s
FROM emps
JOIN depts ON emps.deptno = depts.deptno
WHERE salary > 12000
GROUP BY empid, deptname
Rewrite result
SELECT empid, deptname, SUM(s)
FROM (
  SELECT empid, deptname, s
  FROM mv
  UNION ALL
  SELECT empid, deptname, SUM(salary) AS s
  FROM emps
  JOIN depts ON emps.deptno = depts.deptno
  WHERE salary > 10000 AND salary <= 12000
  GROUP BY empid, deptname) AS subq
GROUP BY empid, deptname