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 would be 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:
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.)
It would be possible to add a facility to the database manager to keep a record of which indexes are ever used. After executing a complete system test, a report would be generated listing the active indexes which are not actually required. These indexes could then be turned off prior to running REORGDB to generate a new and slimmed down database.
It would be possible to add a new dictionary table to the database manager called PC_SUBSTITUTE which would automatically substitute redundant indexes with their replacements. This would be a very powerful enhancement for two reasons:
09 Jan 2002 Downloads updated to fix bug K4000038
Beta software is supplied without warranty and should not be used in live sites without the agreement of Itim Technology Solutions. Compatibility with an eventual final release cannot be guaranteed.