Databases Reference
In-Depth Information
joins when they produce equivalent results, and replacing a view reference in a query
by the actual view definition (called
view merging
).
A very common transformation is the materialized view rewrite. If some part of a
query is equivalent to an existing materialized view, then the code is replaced by that
view. Oracle, for instance, performs a materialized view transformation, then optimizes
both the original query and the rewritten query and chooses the more efficient plan
between the two alternatives.
Rewrites are especially common in data warehouses using the star schema format.
In Oracle, for instance, joins of the fact table with one or more dimension tables are
replaced by subqueries involving both tables, using special (bitmap) indexes on the fact
table for efficiency.
3.2.2 Query Execution Plan Viewing
How do you know what plan your database chose for the most recent query? All mod-
ern database products provide some facility for the user to see the access plan. In DB2
and Oracle it is called Explain or Explain Plan (the graphical version is called Visual
Explain). This facility describes all the steps of the plan, the order in which tables are
accessed for the query, and whether an index is used to access a table. The optimizer
selects the best plan from among the candidate plans generated.
3.2.3 Histograms
Many database systems (e.g., DB2, SQL Server, Oracle) make use of stored histo-
grams of ranges of attribute values from the actual database to help make better esti-
mates of selectivities for selection and join operations, so costs for query execution
plans can be estimated more accurately.
3.2.4 Query Execution Plan Hints
Along with Explain, plan hints have become a major fixture in the database industry,
allowing application programmers (users) to force certain plan choices, removing uncer-
tainty in many cases. Hints are programmer directives to an SQL query that can change
the query execution plan. They are supported by all of the major database systems.
While made widely available, they should be used only when major performance prob-
lems are present. As an example of the use of a hint, a user can set up an experiment to
compare a suboptimal index with an optimal index (and therefore an optimal plan) and
see if the performance difference is worth the overhead to use the optimal index, espe-
cially if it is used only for this query.
