Indexed Sets

Use

Indexed sets can be used to physically link related record occurrences together or to provide alternate access to a record. In an indexed set, a pointer array associated with each owner occurrence contains the db-keys of all related member record occurrences.

Types of Indexed Sets

There are two types of indexed sets:

Set	Description
User-owned	The owner of the set is a user-defined record.
System-owned	The owner of the set is a system-defined SR7 record. The location mode of an SR7 record is CALC on the set name for non-SQL defined indexes or on an internally-generated name for SQL defined indexes. There is at most one occurrence of an SR7 record for each system-owned index.

How to Define an Indexed Set

Use the following clauses on the SET statement to define an indexed set in a non-SQL schema definition:

Set	Description
User-owned	MODE IS INDEX
System-owned	MODE IS INDEX OWNER IS SYSTEM

Set

Description

User-owned

MODE IS INDEX

System-owned

MODE IS INDEX

OWNER IS SYSTEM

Use the following SQL statements to implement an SQL defined constraint as an indexed set:

Set	Description
User-owned	Use this clause on the CONSTRAINT statement: LINKED INDEX
System-owned	Use the CREATE INDEX statement

Set

Description

User-owned

Use this clause on the CONSTRAINT statement:

LINKED INDEX

System-owned

Use the CREATE INDEX statement

When you implement a constraint as an indexed set, the referenced table is the owner of the set and the referencing table is the member.

Set Order

An indexed set can have any of the following set orders: FIRST, LAST, NEXT, PRIOR, or SORTED. If it is SORTED, it can be sorted either on a user-specified symbolic key (sort key) or on the db-key of the member record occurrences.

Using SQL, the set order of a LINKED INDEX constraint is:

SORTED, if you specify the ORDER BY clause
Otherwise, LAST

The set order of an indexed set created using the CREATE INDEX statement is:

SORTED on a symbolic key if you specify the ORDER BY clause
Otherwise, SORTED on db-key

Notation

The following diagram uses standard CA IDMS database notation for two indexed sets, SKILL-EXPERTISE and EMP-LNAME-NDX. The descriptions of the indexed set relationships in the figure include the name of the set, linkage options, membership options, and the sort sequence and symbolic key.

The left side of the figure illustrates an indexed set (the SKILL-EXPERTISE set) between two user-defined record types. The owner of the SKILL-EXPERTISE set is the user-defined SKILL record; the member, EXPERTISE, is the indexed database record.

The right side of the figure illustrates an indexed set (the EMP-LNAME-NDX set) used to place an index on a user-defined member record type. The owner of the EMP-LNAME-NDX set is a system record, represented by a triangle; the member, EMPLOYEE, is the indexed database record.

Indexed Set Occurrence

An indexed set occurrence consists of one occurrence of the owner record, type, an index, and any number of member record occurrences. The owner occurrence contains next and prior pointers to the index; the bottom-level of the index contains the member record occurrences' db-keys in the specified set order. If the indexed set has a user-defined owner record, each member occurrence contains an index pointer to the bottom-level of the index, and optionally, a pointer that links them directly to the owner occurrence. If the indexed set is system-owned, each member occurrence may optionally contain an index pointer.

Note: AN SQL defined linked constraint implemented as an indexed set always has owner pointers.

Basic Structure of an Indexed Set: The member record occurrences in an indexed set point to the index that is chained to the owner record by next, prior, and owner pointers. The owner record contains next and prior pointers that chain it to the index.