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

sequence by primary key? Are the tables small enough to rely on internal
DBMS scanning and sorting? Will indexes help?

Requirement 5 involves scanning the RENTAL-AGREEMENT table. The
important questions here are: will physically storing the rows in a particu-
lar sequence (with or without an index) speed up access? Which sequence
(END-DATE, BEGIN-DATE, or a concatenation of both dates) is best? Should
the rows be stored in ascending or descending order, or is it better to store
the rows in sequence by MONTHLY-RENT-AMT?

The final access mechanism can be used in
combination with the first three storage-related access mechanisms to
tune for a mix of user requirements: adding indexes — step RDD10. Indexes
usually are optional access mechanisms that can complement scanning,
clustering, or hashing. Indexes can significantly increase the ratio of rows
returned to rows searched in order to satisfy a query by enabling direct
access to individual rows, thereby eliminating table scans; reducing the
span of rows searched when a scan is required; avoiding a sort of table
rows (most useful when a small percentage of rows is required in sorted
order); and eliminating table access altogether if required columns are
stored as part of the index.

Tuning by Adding Indexes.

Indexes can be helpful in the implementation supporting Ron's Real
Estate Business. Indexes on the primary key of PROPERTY and on the for-
eign (also primary) keys of BEACH-PROPERTY and MOUNTAIN-PROPERTY
would facilitate the required join to produce a list of all properties with all
details. For time requests, indexes can help the DBMS scan the RENTAL-
AGREEMENT table. Building an index on BEGIN-DATE, on END-DATE, or on
both dates could improve access time.

If Ron is unsure of other processing requirements, a good tuning might
proceed by assuming that he will need to join tables, most likely by pri-
mary-to-foreign key reference paths. An index on each foreign key might be
helpful as well. When a given foreign key is a component of a primary key
(e.g., PROPERTY*STREET-ADDRESS and PROPERTY*TOWN-STATE-
ADDRESS in the RENTAL-AGREEMENT table) and that primary key already
has an index on it (e.g., to enforce uniqueness), the primary key index may
also be used to facilitate foreign key access. Other possibilities include
altering the sequence of columns in the index or a new index built solely on
the foreign key.

Tuning by Introducing Controlled Data Redundancy

The next group of tuning techniques involves altering the relational
database structure to accommodate functional and performance require-
ments. Changing the database structure raises two important issues. First,
it introduces deviations from the stable logical data model; these devia-