====== Indexing on Xlates=====

^Published By^Date^Version^Knowledge Level^Keywords^
|Sprezzatura Ltd|01 AUG 1990|1.16+|EXPERT|INDEXING, XLATE, SYMBOLIC, !INDEXING|

Indexing on Symbolics is one of the most powerful features of AREV indexing
- creating a symbolic which concatenates three fields together and BTREEing
this can dramatically improve the performance of three level sorts when the
required three levels are known in advance.

In an effort to implement databases in a relational manner it is customary
to store foreign keys in data records pointing to data records in an
external table where repeating data can be kept. A classic example of this
would be an employee record containing a departmental ID. Rather than store
all details about the department in the employee record, the department id
is stored and all additional information about the department is XLATEd as
required.

This works in a wholly satisfactory manner until fast reporting is required
on a derived field (for example, sorting the Employees by Department Name).
If a Btree is established on the Department Name symbolic in the Employee
file, the report will run correctly until a department name is changed in
the Department file. When the department name is changed, the Employee
record Department name symbolic is not updated (the only write is that of
the department record so the employee department symbolic is not
reevaluated), and the Employee index becomes out of sync. Thus if there were
three employees in departments as follows, and the list was sorted by a
btreed dept. name

<code>
          BROWN A   100  BOUGHT LEDGER
          SMITH J   200  PERSONNEL
          JONES A   300  STAFF ACCOUNTING
</code>

and the STAFF ACCOUNTING department was changed to PAYROLL the employee list
would now display in an incorrectly sorted order.

<code>
          BROWN A   100  BOUGHT LEDGER
          SMITH J   200  PERSONNEL
          JONES A   300  PAYROLL
</code>

What is required is a method whereby whenever a department name is changed,
all symbolics referring to that field in other files are reevaluated and the
corresponding indexes updated.  To do this several conditions, must be met

  * System must know which records reference the changed record
  * System must know of existence of indexes on external records
  * System must detect change and reevaluate external symbolics

An undocumented feature permits the system to keep track of these details
for the user, subject to the usual restrictions on relational indexes.
(Sticking to the example given above, one department could not have more
than 64K of employees keys related to it).

The steps involved in setting up this feature are straightforward.  Using
the example given above

   * Add BTREE to Department Name symbolic in Employee file and build the index
   * As a relational index is to be set up between the Employee file and the Departments file, add a multivalued field definition (EG EMP.KEYS) to the Department file to store the relationally indexed keys from the Employee file.
   * Define a relational index from the Department Number in the Employees files to the EMP.KEYS field in the Department file. (This is most easily accomplished from Shift-F1 in the DICT window rather than the indexing menu).
   * At TCL, edit the symbolic which XLATES the description (in this case the Department Name Symbolic in the Employees file).  Ensure that no extra lines are inserted or deleted by turning Ins Line off.  Move the cursor to line 21 (the "depends on" field) and define this item as related to the department file by inserting information in the format FILE*FIELD, in this case DEPARTMENTS*EMP.KEYS.  Save this symbolic.  (Try a LISTINDEX on Employees to see "Depens").

The link is now established and the symbolic on the btree will now be
automatically updated.

Note that if more than one independent link is required, Field 21 can be
multivalued.

==== Caveats ====

This method can occasionally "hang" in a networked environment. To consider
the scenario - when you write a record to disk and an index update is
required, SI.MFS locks record 0 in the !INDEXING file, then evaluates the
necessary index updates and writes the transactions to !INDEXING. After the
transactions are written, the lock on !INDEXING is released. Whilst one
station has the lock, other stations cannot save transactions but must wait
for the transaction lock. This is evidenced by the "Waiting for Index
Transaction Lock" message.

If you change a master record which has, for example, 2,000 associated
records, the system must evaluate the results of the change for all 2,000
associated records. The !INDEXING lock will be kept for some time, and other
stations trying to save their records will be delayed. Normally the message
will disappear if left, and is a small price to pay for the convenience.

This problem seems to be worse in releases prior to 2.0.


(Volume 2, Issue 4, Pages 5,6)