Internal Language Transformations
Page 4 of 8

View Transformations

When Cloudscape evaluates a statement that references a view, it transforms the reference to a view into a derived table. It may make additional transformations to improve performance.

View Flattening

When evaluating a statement that references a view, Cloudscape internally transforms a view into a derived table. This derived table may also be a candidate for flattening into the outer query block.

A view or derived table can be flattened into the outer query block if all of the following conditions are met:

  • The select list is composed entirely of simple column references and constants.
  • There is no GROUP BY clause in the view.
  • There is no DISTINCT in the view.

For example, given view v1(a,b):

SELECT Hotels.hotel_name, Cities.city_id
FROM Hotels, Cities
WHERE Hotels.city_id = Cities.city_id

and a SELECT that references it:

SELECT a, b
FROM v1 WHERE a = 'Hotel du Quai Voltaire'

after the view is transformed into a derived table, the internal query is

SELECT a, b
FROM (select Hotels.hotel_name, Cities.city_id
FROM Hotels, Cities
WHERE Hotels.city_id = Cities.city_id) v1(a, b)
WHERE a = 'Hotel du Quai Voltaire'

After view flattening it becomes

SELECT Hotels.hotel_name as a, Cities.city_id AS b
FROM Hotels, Cities
WHERE Hotels.city_id = Cities.city_id
AND Hotels.hotel_name = 'Hotel du Quai Voltaire'

Predicates Pushed into Views or Derived Tables

An SQL-J statement that references a view may also include a predicate. Consider the view v2 (a,b):

CREATE VIEW v2(a,b) AS
SELECT city_id, MAX(normal_rate)
FROM Hotels
GROUP BY city_id

The following statement references the view and includes a predicate:

SELECT *
FROM v2
WHERE a = 2

When Cloudscape transforms that statement by first transforming the view into a derived table, it places the predicate at the top level of the new query, outside the scope of the derived table:

SELECT a, b
FROM (SELECT city_id, MAX(normal_rate) FROM Hotels GROUP BY city_id) v2(a, b)
WHERE a = 2

In the example in the preceding section (see View Flattening), Cloudscape was able to flatten the derived table into the main SELECT, so the predicate in the outer SELECT could be evaluated at a useful point in the query. This is not possible in this example, because the underlying view does not satisfy all the requirements of view flattening.

However, if the source of all of the column references in a predicate is a simple column reference in the underlying view or table, Cloudscape is able to push the predicate down to the underlying view. Pushing down means that the qualification described by the predicate can be evaluated when evaluating the view is being evaluated, which is more efficient. In our example, the column reference in the outer predicate, a, in the underlying view is a simple column reference to the underlying base table. So the final transformation of this statement after predicate push-down is

SELECT a, b
FROM (SELECT city_id, MAX(normal_rate) FROM Hotels WHERE city_id = 2 GROUP BY city_id) v1(a, b)

Without the transformation, Cloudscape would have to scan the entire table t1 to form all the groups, only to throw out all but one of the groups. With the transformation, Cloudscape is able to make that qualification part of the derived table.

If there were a predicate that referenced column b, it could not be pushed down, because in the underlying view, column b is not a simple column reference.

Predicate push-down transformation includes predicates that reference multiple tables from an underlying join.