REVMEDIA FKB Articles - AREV, OI, REV G, jREV, JCDC, LIVEWATER,ADVANCED REVELATION, OPENINSIGHT, REVELATION G

RevMedia FKB

Document	V2I4A3
Title	Indexing on Xlates
Keywords	INDEXING XLATE SYMBOLIC !INDEXING
Text	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 FILEFIELD in this case DEPARTMENTSEMP 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)

[revmedia/copyrigh.htm]

Page last modified: 30/01/03