View costs

A complex view could run more slowly than expected.

You can create views of tables for a number of reasons:

To limit the data that a user can access
To reduce the time that it takes to write a complex query
To hide the complexity of the query that a user needs to write

However, a query against a view might execute more slowly than expected when the complexity of the view definition causes a temporary table to be created to process the query. This temporary table is referred to as a materialized view. For example, you can create a view with a union to combine results from several SELECT statements.

The following sample SQL statement creates a view that includes unions:

CREATE VIEW view1 (col1, col2, col3, col4)
   AS 
      SELECT a, b, c, d
         FROM tab1 WHERE 
      UNION
      SELECT a2, b2, c2, d2
         FROM tab2 WHERE
...
      UNION
      SELECT an, bn, cn, dn
         FROM tabn WHERE
;

When you create a view that contains complex SELECT statements, the end user does not need to handle the complexity. The end user can just write a simple query, as the following example shows:

SELECT a, b, c, d
   FROM view1
      WHERE a < 10;

However, this query against view1 might execute more slowly than expected because the database server creates a fragmented temporary table for the view before it executes the query.

Another situation when the query might execute more slowly than expected is if you use a view in an ANSI join. The complexity of the view definition might cause a temporary table to be created.

To determine if you have a query that must build a temporary table to process the view, execute the SET EXPLAIN statement. If you see Temp Table For View in the SET EXPLAIN output file, your query requires a temporary table to process the view.