In designing an unlinked relationship, you must define an index or calc key on the foreign key of the relationship. If you use an index to enforce the integrity of the referential constraint, it must contain the columns that make up the foreign key but can contain additional columns. Defining additional columns after the foreign key columns has the potential for reducing disk space requirements and improving performance.
The ability to extend foreign key indexes with additional columns may enable one index to be used for multiple purposes. For example, a table’s primary key is often a concatenation of one of its foreign keys with additional columns that together form a unique identifier for each row of the table. A single index can be used to enforce both the integrity of the referential constraint and the uniqueness of the primary key. By eliminating a second index you reduce disk space requirements and the overhead associated with index maintenance.
Including extra columns in a foreign key index may also improve access efficiency by enabling the use of more index scans to identify rows matching selection criteria. The use of an index scan can significantly reduce the number of I/Os needed to satisfy a query.
Defining additional columns in the index key
To define additional columns in the index key, define an index so that the foreign key columns precede any additional columns in the index key. The order of the foreign key columns in the index key must match the order of the referenced columns in some unique index or CALC key on the referenced table.
|
Copyright © 2014 CA.
All rights reserved.
|
|