Oracle 5.0, Справочное руководство

Страница: 3006 / 3232

Restrictions on Views
2986
View processing is not optimized:
• It is not possible to create an index on a view.
• Indexes can be used for views processed using the merge algorithm. However, a view that is
processed with the temptable algorithm is unable to take advantage of indexes on its underlying
tables (although indexes can be used during generation of the temporary tables).
Subqueries cannot be used in the
FROM
clause of a view.
There is a general principle that you cannot modify a table and select from the same table in a
subquery. See
Section E.3, “Restrictions on Subqueries” .
The same principle also applies if you select from a view that selects from the table, if the view selects
from the table in a subquery and the view is evaluated using the merge algorithm. Example:
CREATE VIEW v1 AS
SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a);
UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
If the view is evaluated using a temporary table, you can select from the table in the view subquery
and still modify that table in the outer query. In this case the view will be stored in a temporary table
and thus you are not really selecting from the table in a subquery and modifying it “at the same time.”
(This is another reason you might wish to force MySQL to use the temptable algorithm by specifying
ALGORITHM = TEMPTABLE
in the view definition.)
You can use
DROP TABLE
or
ALTER TABLE
to drop or alter a table that is used in a view definition.
No warning results from the
DROP
or
ALTER
operation, even though this invalidates the view. Instead,
an error occurs later, when the view is used.
CHECK TABLE
can be used to check for views that have
been invalidated by
DROP
or
ALTER
operations.
A view definition is “frozen” by certain statements:
• If a statement prepared by
PREPARE
refers to a view, the view definition seen each time the
statement is executed later will be the definition of the view at the time it was prepared. This is true
even if the view definition is changed after the statement is prepared and before it is executed.
Example:
CREATE VIEW v AS SELECT RAND();
PREPARE s FROM 'SELECT * FROM v';
ALTER VIEW v AS SELECT NOW();
EXECUTE s;
The result returned by the
EXECUTE
statement is a random number, not the current date and time.
• If a statement in a stored routine refers to a view, the view definition seen by the statement are its
definition the first time that statement is executed. For example, this means that if the statement
is executed in a loop, further iterations of the statement see the same view definition, even if the
definition is changed later in the loop. Example:
CREATE VIEW v AS SELECT 1;
delimiter //
CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 5 DO
SELECT * FROM v;
SET i = i + 1;
ALTER VIEW v AS SELECT 2;
END WHILE;
END;
//
delimiter ;
CALL p();