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
BROWN A 100 BOUGHT LEDGER SMITH J 200 PERSONNEL JONES A 300 STAFF ACCOUNTING
and the STAFF ACCOUNTING department was changed to PAYROLL the employee list would now display in an incorrectly sorted order.
BROWN A 100 BOUGHT LEDGER SMITH J 200 PERSONNEL JONES A 300 PAYROLL
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)