Databases Reference
In-Depth Information
•
Tip 2. Is workload compression required?
Depending on the utility you are
using, it may or may not offer workload compression. Products that offer com-
pression allow you to submit massive workloads with thousands of statements
without any filtering on your part. Without compression most physical database
design utilities (especially when searching for materialized views) will spend too
much time during analysis to be useful. In those cases you would be best to filter
the queries yourself and supply no more than a few dozen statements as input to
the utility. Yes, this does conflict with Tip 1, and it's a practical reality.
•
Tip 3. Don't limit yourself to indexes.
Many vendors now offer combined sup-
port for materialized views as well as indexes, and the DB2 Design Advisor even
offers MDC and shared-nothing partitioning. Many DBAs and database designers
limit their use of design features to indexes, and it's a great shame. There are orders
of magnitude in benefits to be had by exploiting the richness of the additional fea-
tures, especially when the selection problem is simplified by the aid of automation
tools, so why not try them?
•
Tip 4. Apply judgment and testing.
The physical design problem is extremely
complex, and while automation tools can evaluate thousands of alternatives, they
are not flawless. Treat the recommendations from these tools as “really great advice
to be considered and tested” rather than absolute infallible truth.
•
Tip 5. Remember that your database is a moving target.
It is futile to optimize
to perfection at a point in time because data and applications evolve. Near optimal
is usually plenty sufficient in practice. Many industrial systems are deeply flawed
(conceptual models, software engineering practices, db tuning, etc.) and far from
optimal in practice. As a result take “comprehensive” optimization with a heavy
dose of reality, and avoid the changes that offer only the most temporary and
incremental gains.
•
Tip 6: Remember to include INSERT, UPDATE and DELETE statements.
Automated design tools, like most things in compter science, are only as good as
the input they get. Like the saying goes "garbage in, garbage out". While query per-
formance is often the primary motivator for invoking an automated design advisor,
it's important for the advisor to see the write activity in the workload (INSERT,
UPDATE and DELETEs) so it won't recommend database designs that have
extremely negative impact on them. All the major vendors have taken steps to
include technology in their advisors to factor in the impact on write operations, but
that technology is only effective if the write operations are part of the input.
•
Tip 7. Constrain the advisors as you would yourself.
In previous chapters we rec-
ommended setting reasonable limits on the amount of disk space used for auxil-
liary structures like indexes, MDC, and materialized views. For example, storage
space for indexes should rarely be more than 20-25% of total database storage.
Use automated design advisors wisely by applying the same kind of common
sense. Don't let an automated utility convince you to double or triple the size of
your database so that dozens of indexes and materialized views can be created.
