Designing Relational Databases - High-Performance Web Databases: Design, Development, and Deployment - page 400

access mechanisms the relational DBMS is to use in satisfying a given
query. That decision is left to the product's optimizer. Thus the designer
can introduce changes in the access mechanisms without necessitating
changes in application code or in users' use of SQL or other commands.
The designer must become familiar with the performance benefits and
implications of various access mechanisms. Particularly during an initial
design project using a new product, it is important to experiment with
access mechanisms early so that the design can leverage the performance
strengths and weaknesses of the product. It is also necessary to under-
stand the performance limitations of the facilities in the DBMS before mod-
ifying the design.
Access mechanisms rely on and benefit from the choice of storage
options for each table. The necessary steps are:
• Tuning for scan efficiency — step RDD7.
• Defining clustering sequences — step RDD8.
• Defining hash keys — step RDD9.
To apply these steps to Ron's Real Estate Business example, it is neces-
sary to understand more about his processing requirements. Some of Ron's
requirements are that:
1. When a renter's name is given, the address is returned.
2. When a renter's name is given, the telephone number is returned.
3. When a monthly rental figure is given, the names of all potential
renters willing to pay that rent are returned.
4. All properties with all associated detail information are listed.
5. Properties that will be vacant as of a specific date are listed in de-
scending order by monthly rental amount.
Alternatives exist for optimizing cost and performance trade-offs for
each of these requirements. Because requirements 1 and 2 involve direct
random access to individual rows, hashed access may be the most efficient
alternative. Requirement 3 involves a qualification on a range of values
(i.e., which renters are willing to pay maximum monthly rental amounts
equal to or greater than some specified figure.) Storing RENTER rows in
sorted sequence by MAX-MONTHLY-RENT-AMT may accelerate the pro-
cessing of this requirement. Because the RENTER table cannot be both
hashed and clustered, however, compromises must be made. The speed
with which the DBMS can scan the entire table and the table can be sorted
must be examined. The relative importance of each requirement and its
performance objectives and constraints must be weighed.
Requirement 4 entails joins of PROPERTY with BEACH-PROPERTY and
PROPERTY with MOUNTAIN-PROPERTY. Some relevant questions to con-
sider are: can the speed of access be increased by storing the rows in