Interpreting Queries Involving Views

Interpreting Queries Involving Views

We can get a good idea of what view queries mean by following the way a query involving a view would be processed. The matter is taken up in more generality in "Algebraic Laws for Improving Query Plans", when we study query processing in general.

The basic idea is shown in Figure 1. A query Q is there represented by its expression tree in relational algebra. This expression tree uses as leaves some relations that are views. We have suggested two such leaves, the views V and W. To interpret Q in terms of base tables, we find the definition of the views V and W. These definitions are also expressed as expression trees of relational algebra.

Substituting view definitions for view references

To form the query over base tables, we substitute, for each leaf in the tree for Q that is a view, the root of a copy of the tree that defines that view. Therefore, in Figure 1 we have illustrated the leaves labeled V and W replaced by the definitions of these views. The resulting tree is a query over base tables that is equivalent to the original query about views.

Example 1 : Let us look at the view definition and query of "View Definitions" Example 2. Remember the definition of view ParamountMovie is:

CREATE VIEW ParamountMovie AS
     SELECT title,  year
     FROM Movie
     WHERE studioName = 'Paramount';

An expression tree for the query that defines this view is illustrated in Figure 2. The query of "View Definitions" Example 2 is

Expression tree for view ParamountMovie

SELECT title
FROM ParamountMovie
WHERE year = 1979;

asking for the Paramount movies made in 1979. This query has the expression tree illustrated in Figure 3. Note that the one leaf of this tree represents the view ParamountMovie.

Expression tree for the query

We therefore interpret the query by substituting the tree of Figure 2 for the leaf ParamountMovie in Figure 3. The resulting tree is illustrated in Figure 4.

The tree of Figure 4 is an acceptable interpretation of the query. On the other hand, it is expressed in an unnecessarily difficult way. An SQL system would apply transformations to this tree in order to make it look like the expression tree for the query we suggested in "View Definitions" Example 2:

SELECT title
FROM Movie
WHERE studioName = 'Paramount' AND year = 1979;

For instance, we can move the projection πtitle, year above the selection σyear=1979. The reason is that delaying a projection until after a selection can never change the result of an expression. Then, we have two projections in a row, first onto title and year and then onto title alone. Clearly the first of these is redundant, and we can remove it. Thus: the two projections can be replaced by a single projection onto title.
Expressing the query in terms of base tables

The two selections can also be combined. Normally, two consecutive selections can be replaced by one selection for the AND of their conditions. The resulting expression tree is illustrated in Figure 5. It is the tree that we would obtain from the query directly.

SELECT title
FROM Movie
WHERE studioName = 'Paramount' AND year = 1979;


Simplifying the query over base tables


Tags