For the purposes of this page, Views in Oracle comprise the explicit kind of view that you create with the CREATE OR REPLACE VIEW syntax, as well as the implicit kind - variously known as implicit views, nested queries, or inline views - where you specify a query in place of a table or view name within a SQL.
The following is taken from Oracle's Designing and Tuning for Performance guide, Transformaing and Optimizing Statements.
To optimize a statement that accesses a view, the optimizer chooses one of the following:
- Transform the statement into an equivalent statement that accesses the view’s base tables, then optimize the resulting statement. The optimizer can use one of the following techniques to transform the statement:
- Merge the view’s query into the referencing query block in the accessing statement.
- Push the predicate of the referencing query block inside the view (for an non-mergeable view).
- Issue the view’s query, collecting all the returned rows, and then access this set of rows with the original statement as though it were a table.
The first option certainly sounds better. Some of the possibilities for better performance include:
As stated above, when Oracle transforms a query on a view it can either merge or push predicates. Of the two techniques, merging provides better optimization opportunities, but is more restrictive in the situations where it can be used.
If your query uses any of these features, read on below to see whether Push Predicates can help. One exception to this rule is views using the UNION ALL set operator that also satisfy the criteria for a Partition View.
If your view uses either DISTINCT or GROUP BY, then Complex View Merging may provide better performance. Check with the DBA to see whether either the OPTIMIZER_FEATURES_ENABLE or COMPLEX_VIEW_MERGING initialisation parameters are set. If not, you can enable complex view merging for just your query using the MERGE hint (not to be confused with the USE_MERGE hint which is used for Sort-Merge joins). Note that Complex View Merging will not help for queries that access columns in the view built from aggregate or analytic functions.
If your view is joined as the outer table of an outer join, then it cannot be merged if the view has two or more base tables, however it can still Push Predicates (see below).
If you think your query can use View Merging or Complex View Merging, then it can be difficult to prove whether or not Oracle is actually doing it. If the result of the merged query would allow the use of a particular index or partition key (for pruning), then this will show up in the Explain Plan. Otherwise, you could add a ROWNUM column to the view (which would disallow merging altogether) and try again. If the performance was markedly slower, then the original query was probably performing a merge or at least pushing predicates (see below).
Whereas View Merging rewrites the query dispensing with the view entirely, Push Predicates leaves the view in place, but uses one or more of the predicates in the outer query to restrict the rows returned (and possibly provide a better execution path).
Predicates that can be pushed into a view include:
There are some circumstances where Oracle cannot push predicates. It is difficult to obtain a definitive list of these situations, but here is a start:
Although it is unstated in the Oracle documentation, you may also have difficulty pushing predicates into some views with UNION, INTERSECT or MINUS.
If your view is unable to either merge or push predicates, yet you are providing a predicate in the outer query that you believe could improve performance if it were inside the view, then you could try one of the following