Index Link Scrutiny

You can access SETLINK from within MAKEDB, or directly from the command line or the DP4 menu.

  1. When you enter SETLINK, (and after selecting a database if necessary), you are presented with a menu containing five options (including exit). Both the first two options allow you to set the link properties. The first option displays all the indexes and parents for a particular table. The second option displays all the tables that a particular table is a parent of or index for. The second option will be particularly useful when you have modified a database by adding a new index table. Select either option 1 or option 2.

  2. DP4 automatically displays the first of the tables for which you can set index characteristics.

  3. Press <Enter> to accept the table name, or enter another table name. DP4 displays the current index settings for the table you select. Change the index settings between the tables in your database as required for your application.

    Click on features of the example Index Settings screen above for further details.

  4. When you complete the index link settings, press <Esc> several times to return to the DP4 System Main menu.

    You have completed the database creation process. Before you take any other action, back up your database.

SETLINK contains two more options: the Redundant Index Report, and Automatically turn off unused indexes.These are explained below.

Redundant Index Report

Large DP4 databases often contain a great many tables and indexes, and it is by no means uncommon for it to become very unclear whether a particular index is required or not. SETLINK has been enhanced to generate a report on "redundant" indexes. The indexes are redundant in the sense that there is another index which will return the same data in the same order, and that therefore one of the indexes can be dispensed with provided any programs that use the index to be turned off are updated as appropriate. A database with redundant indexes is larger and slower than it would be otherwise. The report does not identify indexes that are turned on but which are never actually used, as there is currently no way to determine this. It is possible to identify which indexes are not used by QAB or Report Writer, but not indexes that are used by C /C++ programs,or SQL.

For example suppose a database has a table containing these tables and fields:

  1. ORDER containing foreign keys ITEM_NR and ORDER_DATE
  2. ITEM with key fields ITEM_NR
  3. INDEX_ITEM_DATE with key fields ITEM_NR and ORDER_DATE

Both the ITEM and INDEX_ITEM_DATE tables index the ORDER table. However there is probably no need to enable both indexes, because the INDEX_ITEM_DATE will return ORDERS in ITIM_NR sequence in any case. Unfortunately the ITEM index IS required if child deletion or orphan checking from ITEM to ORDER is required.

The Redundant Index Report in SETLINK identifies and lists all indexes for which an alternative index can be used instead. The report contains pairs of lines, the first line lists the redundant index, the second an index which can be used as an alternative. (There may be several such indexes, in which case the one with the most keys is listed). The child delete and orphan check settings for the redundant index are also listed. If both of these set are set to NO then the index is definitely redundant. If either is set to Yes the index is not strictly speaking redundant because it is required for these purposes, but you may be able to live without it if you can dispense with these options for this table.

You may occasionally find that the primary index of a table is listed as being redundant. In this case it is more likely that you will wish to switch off the secondary index, as it cannot add any further ordering information to the records in the table. At worst you may need to add a further test to programs that do EQUAL searches on the secondary index. (For example a CREDIT_CARD table might have a primary key of CARD_NR and a secondary index of CARD_NR and STOPPED, so that a program could check it was not stopped with a view to doing a NODATA search on the secondary index. However, in practice it would be better to just inspect the STOPPED field after accessing the record via the primary index.)

Automatically Turn off Unused Indexes

This option, introduced in releae 4.621, is particularly useful where the number of secondary indexes that are turned on has become excessive and performance of DP4 has been adversely affected.

This option switches off all secondary indexes that are not needed for automatic child deletion and are not used by either QAB or Report Writer. A report is produced detailing the changes made, so you can easily use options 1 or 2 of SETLINK to turn on any indexes you are sure you really want to keep.

After running this option you will normally want to do a full reorganisation of the database to reclaim the space used by indexes that have now been switched off.

Using this option might cause some programs to stop working with system error 24, and might degrade the performance of some SQL queries. Also the operation of RECASTDB using a configuration file may be upset.

You can fix any problems of this nature that arise by turning the problem index back on again. You will have to reorganise the affected index afterwards.