Databases Reference
In-Depth Information
Figure 11.8
Query execution plans before (
left
) and after (
right
) MQT3 for the second query.
plan shows a table scan on the DEPARTMENT table that appears to consume a signif-
icant percentage of the total resource consumption for the query. If the qualifying rows
from DEPARTMENT are a small percentage of the total table size (much less than
50%), an index on DEPARTMENT can further reduce the cost of access.
The great value of materialized views is that a single view can provide benefits to
multiple query statements. Consider the same materialized view MQT3 above with a
new query:
select workdept, job, count(*) as ct from employee where
salary > 1000 group by job, workdept;
The before and after query execution plans are shown in Figure 11.8. Again, we see
a huge performance difference between the before and after resource consumption:
1,979 versus 26 units, suggesting a 76 times resource consumption reduction and a
potential corresponding speedup of query execution. Two very important points are
worth noting: first, that the same materialized view was useful to two rather different
queries, and second, that in both cases the text of the queries made no reference to the
materialized view MQT3. In both cases the detection that MQT3 was useful and the
decision to exploit the materialized view in the access path were made entirely by the
