Databases Reference
In-Depth Information
Figure 12.20
Experimental results for automated MDC design.
CUST3 has an unusual result where the hand-tuned design was actually worse
than the default design and the DTA design offers no improvement. This was due to
the presence of a large amount of UPDATE activity in the workload. DTA correctly
recognized that the benefit of indexes and materialized views would be offset by a
negative impact on UPDATE performance, and therefore recommended no design
changes over the default. The hand-tuned design which included some additional
indexes suffered overall because every UPDATE become slightly slower due to the
additional index maintenance.
Table 12.5
Overview of Internal Customer Databases and Workloads
To t a l s i z e
(GB)
Database
#DBs
#Tables
#SQL statements tuned
CUST1
10.7
31
4374
15K
CUST2
1.4
6
48
252K
CUST3
105.9
23
1624
176K
CUST4
0.06
2
11
9K
Chaudhuri et al. [2002] published results on the effectiveness of workload com-
pression in speeding up the search time for design utilities while having a relatively
small impact on the quality of the results. Figure 12.21 is a reproduction of their
