View Processing Algorithms
1701
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
, MySQL 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 the
CREATE VIEW
statement.
• The
CREATE VIEW
statement has an explicit
ALGORITHM = UNDEFINED
clause.
•
ALGORITHM = MERGE
is specified for a view that can be processed only with a temporary table. In
this case, MySQL generates a warning and sets the algorithm to
UNDEFINED
.
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
becomes
t
•
*
becomes
vc1, vc2
, which corresponds to
c1, 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
[880]
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()
[972]
,
MIN()
[971]
,
MAX()
[971]
,
COUNT()
[970]
, and so forth)
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
