Database Reference
In-Depth Information
View Merging
The purpose of
view merging
is to reduce the number of query blocks due to views and inline views by merging
several of them together. This query transformation was introduced because, without it, the query optimizer would
process each query block separately. When processing query blocks separately, the query optimizer can't always find
an execution plan that is optimal for the SQL statement as a whole. In addition, the query block resulting from view
merging might enable further query transformations to be considered.
QUerY BLOCKS
Simply put, top-level SQL statements and each additional portion of a SQL statement with its own
SELECT
clause
are query blocks. Simple SQL statements have a single query block. multiple query blocks exist whenever views
or constructs such as subqueries, inline views, and set operators are used. For example, the following query has
two query blocks (I'm using the subquery factoring clause instead of defining a real view for illustration purposes
only). the first query block is the top-level query, the one that references the
dept
table. the second query block
is the query defined in the
WITH
clause, the one that references the
emp
table:
WITH emps AS (SELECT deptno, count(*) AS cnt
FROM emp
GROUP BY deptno)
SELECT dept.dname, emps.cnt
FROM dept, emps
WHERE dept.deptno = emps.deptno
View merging has two subcategories:
•
Simple view merging
is used for merging plain, select-project-join query blocks.
1
Because of the
simplicity of the cases it handles, simple view merging is a heuristic-based query transformation.
It can't be applied to views or inline views that contain constructs like aggregations, set
operators, hierarchical queries, the
MODEL
clause, or subqueries in the
SELECT
list.
•
Complex view merging
is used for merging query blocks that contain aggregations. It is a
cost-based query transformation that can't be applied to views or inline views that, for example,
either appear in hierarchical queries or contain
GROUPING SETS
,
ROLLUP
,
PIVOT
, or
MODEL
clauses.
Note that complex view merging is a cost-based query transformation because applying it isn't always beneficial.
In fact, when it's applied, the aggregation present in the merged view or inline view is postponed and, therefore, might
be executed on a larger result set.
View merging can introduce security issues. To prevent them, the concept of
secure view merging
, which is
controlled by the
optimizer_secure_view_merging
initialization parameter, is available. Chapter 9 describes this
feature in detail.
1
A select-project-join query block is made up of three basic operations: a selection that extracts rows fulfilling specific predicates, a
projection that extracts specific columns from the referenced tables, and a join that puts together data extracted from several tables.
