Physical Database Design

210 CHAPTER 11 Query Execution Plans and Physical Design

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 ...