Choosing Physical Tuning Options › Relationship Tuning Options › Linked Relationship Tuning Options › Sorted and Unsorted Relationships

Sorted and Unsorted Relationships

You can specify the logical order of child occurrences within each linked relationship:

• Sorted— A new entity occurrence is positioned according to the value of one or more of its data elements (called a sort key) relative to the values of the same data elements in other related child occurrences.
• Unsorted— A new entity occurrence is positioned according to a predefined order within the relationship.

  For example, all new entity occurrences might be positioned ahead of all existing occurrences.

Advantages of a sorted relationship

Through a sorted relationship:

• A program can retrieve a child occurrence directly by key, thus reducing CPU.
• A program can retrieve child occurrences data in order, thus avoiding sorts.
• Unique constraints can be enforced without the need for additional indexes.

Considerations for sorted relationships

Maintaining the relationship's order during update operations requires increased CPU and a greater number of I/Os than an unsorted relationship.

Enforcing unique constraints

Sorted relationships can be used to enforce unique constraints as an alternative to a CALC key or index. For example, you can eliminate the EXP-NDX index in the Commonweather Corporation by defining either the SKILL-EXPERTISE or the EMP-EXPERTISE relationship as a unique sorted relationship.

To eliminate the index, you must either:

• Define SKILL-EXPERTISE as sorted on EMP ID with the unique option.

  or

• Define EMP-EXPERTISE as sorted on SKILL CODE with the unique option

Either approach ensures that no employee is assigned duplicate skills.

Sorted order

You can choose to sort in ascending, descending, or mixed order.

As a general rule, choose the sort order to reflect the most commonly desired retrieval order. However, the sequence chosen for a chained relationship can have an impact on performance in update transactions. This will allow the DBMS to locate the point of insertion more quickly.

If new entity occurrences typically have sort key values greater than existing occurrences, the relationship should have a descending sort order. Conversely, if new occurrences have sort keys lower than existing occurrences, ascending is preferable.

For example, new occurrences of dated entities are usually stored with higher dates than previously stored occurrences. If this is the case, you should specify descending for a chained relationship sorted by date.

More Information

For more information concerning the usage of numeric fields as part of a sort key, see Zoned and Packed Decimal Fields as IDMS Keys.