Databases Reference
In-Depth Information
Figure 11.6
Details for Explain on operator #15. (Image courtesy of IBM.)
PS_AVAILQTY column. This suggests strongly that by adding the PS_AVAILQTY
column to this index we can avoid access to the PARTSUPP table in this subplan,
thereby improving performance.
Next, we'll examine the use of a materialized view (see Chapter 5) to demonstrate
how that changes the access path and how we can use the access path to confirm that
the new materialized view is both used by the database at runtime and provides value.
select * from department where 10 in (select count(*)
from employee where admrdept = workdept and salary >
1000 group by job);
A materialized view is designed for this query as follows (using DB2 syntax):
•
Create a materialized view named MQT3; create table MQT3 as (select
workdept, job, count(*) as ct from employee where salary > 1000 group by job,
workdept) data refresh immediate in userspace1.
•
Create an index on this materialized view, on the workdept column; create
index IDX3 on MQT3(workdept).
•
Update the statistics for this materialized view in the system catalogs; runstats
on table MYSCHEMA.MQT3 and indexes all; commit.
Figure 11.7 shows the access paths before and after creating the materialized view.
In this example we are using the DB2 Visual Explain tooling. In this format each node
in the access path is assigned a number in brackets that identified the operator for fur-
ther detail. The second number beside each operator is an approximate cost for the
operator and all its suboperators within its branch of the access path. The figure on the
left shows the access path before the materialized view was created, showing table scans
