Additional info on View Merging

Each view referenced in a query is expanded by the parser into a separate query block. The query block essentially represents the view definition, and therefore the result of a view. One option for the optimizer is to analyze the view query block separately and generate a view subplan. The optimizer then processes the rest of the query by using the view subplan in the generation of an overall query plan. This technique usually leads to a suboptimal query plan, because the view is optimized separately from rest of the query.

The query transformer then removes the potentially suboptimal plan by merging the view query block into the query block that contains the view. Most types of views are merged. When a view is merged, the query block representing the view is merged into the containing query block. Generating a subplan is no longer necessary, because the view query block is eliminated.

Grant the MERGE ANY VIEW privilege to a user to enable the optimizer to use view merging for any query issued by the user. Grant the MERGE VIEW privilege to a user on specific views to enable the optimizer to use view merging for queries on these views. These privileges are required only under certain conditions, such as when a view is not merged because the security checks fail.