View Processing Algorithms
The optional ALGORITHM
clause for CREATE VIEW
or ALTER VIEW
is a MariaDB extension to standard SQL. It affects how MariaDB processes the view. ALGORITHM
takes three values: MERGE
, TEMPTABLE
, or UNDEFINED
. The default algorithm is UNDEFINED
if no ALGORITHM
clause is present.
For MERGE
, the text of a statement that refers to the view and the view definition are merged such that parts of the view definition replace corresponding parts of the statement.
For TEMPTABLE
, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
For UNDEFINED
, MariaDB chooses which algorithm to use. It prefers MERGE
over TEMPTABLE
if possible, because MERGE
is usually more efficient and because a view cannot be updatable if a temporary table is used.
A reason to choose TEMPTABLE
explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE
algorithm so that other clients that use the view are not blocked as long.
A view algorithm can be UNDEFINED
for three reasons:
- No
ALGORITHM
clause is present in theCREATE VIEW
statement. - The
CREATE VIEW
statement has an explicitALGORITHM = UNDEFINED
clause. ALGORITHM = MERGE
is specified for a view that can be processed only with a temporary table. In this case, MariaDB generates a warning and sets the algorithm toUNDEFINED
.
As mentioned earlier, MERGE
is handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how the MERGE
algorithm works. The examples assume that there is a view v_merge
that has this definition:
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100;
Example 1: Suppose that we issue this statement:
SELECT * FROM v_merge;
MySQL handles the statement as follows:
v_merge
becomest
*
becomesvc1, vc2
, which corresponds toc1, c2
- The view
WHERE
clause is added
The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE c3 > 100;
Example 2: Suppose that we issue this statement:
SELECT * FROM v_merge WHERE vc1 < 100;
This statement is handled similarly to the previous one, except that vc1 < 100
becomes c1 < 100
and the view WHERE
clause is added to the statement WHERE
clause using an AND
connective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes:
SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100);
Effectively, the statement to be executed has a WHERE
clause of this form:
WHERE (select WHERE) AND (view WHERE)
If the MERGE
algorithm cannot be used, a temporary table must be used instead. MERGE
cannot be used if the view contains any of the following constructs:
- Aggregate functions (
SUM()
,MIN()
,MAX()
,COUNT()
, and so forth) DISTINCT
GROUP BY
HAVING
LIMIT
UNION
orUNION ALL
- Subquery in the select list
- Refers only to literal values (in this case, there is no underlying table)