Databases Reference
In-Depth Information
Table 4.1
I/O Time Comparison for Example Queries 4.1 and 4.2
High-selectivitiy Query (4.1)
Join Strategy
Range of Performance
Best Performance
Nested-loop join
112-290 seconds
Project is outer loop
Block nested-loop join
.45-.89 seconds
Project is outer loop
Sort-merge join
.45-3.98 seconds
No sorting required
Low-selectivity Query (4.2)
Join Strategy
Range of Performance
Best Performance
Indexed nested-loop
.46-.63 seconds
Both tables indexed, ntr = 100
Hash join
.45-1.36 seconds
Hash partitions in memory
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. The number and sizes of indexes varies.
However as a general rule indexes
usually consume between 10% and 20% of the database disk storage. More than
about 25% of disk being used for indexes should raise eyebrows and motivate a sober
second look.
•
Tip 2. Indexes help queries and hurt write operations.
There-fore databases that are
query heavy can err on slightly more in-dexes, and databases that either have a lot of
insert/update/delete activity, or for whom performance of these write activities are
very important, should be more conservative in the number of indexes they define for
the system.
•
Tip 3. Review all the indexing rules of thumb listed in Section 4.2.
This section con-
tains 12 practical rules for selecting indexes.
4.5
Summary
This chapter discusses the relationships between the different types of queries, indexing
methods, and access methods. Knowledge of these relationships and what makes an
index effective is very helpful in the decision-making process for index selection. There
are seven critical index design decisions:
1.
Whether or not an index is required, and if so which search key to use.
2.
When to use a multi-attribute (composite) index and which search keys to use.
