View composite implementation

 

The view composite implementation takes the query select statement and combines it with the select statement of the view to generate a new query. The new, combined select statement query is then run directly against the underlying base tables.

This single, composite statement is the preferred implementation for queries containing views, since it requires only a single pass of the data.

See the following examples:

CREATE VIEW  D21EMPL AS 	
  SELECT * FROM CORPDATA.EMPLOYEE   WHERE WORKDEPT='D21'

Using SQL:

  SELECT LASTNAME, FIRSTNME, SALARY   FROM D21EMPL   WHERE JOB='CLERK'  

The query optimizer will generate a new query that looks like the following example:

SELECT LASTNAME, FIRSTNME, SALARY 	
  FROM CORPDATA.EMPLOYEE   WHERE WORKDEPT='D21' AND JOB='CLERK'  

The query contains the columns selected by the user's query, the base tables referenced in the query, and the selection from both the view and the user's query.

The new composite query that the query optimizer generates is not visible to users. Only the original query against the view will be seen by users and database performance tools.

 

Parent topic:

View implementation