Creating Matchcode Indexes

When you activate a transparent matchcode ID, a check is made to see whether a suitable database index exists for this ID. Such an index is generally necessary to support matchcode selection. If no such index exists, considerable performance problems could result during matchcode selection.

The system assumes that the first field of the matchcode definition (the first field after the client field in cross-client matchcodes) is the relevant search field for this matchcode, that is, that the user limits this field during the matchcode search by entering a selection value. An index is considered to be suitable if it contains the relevant matchcode search field (possibly after a client field).

If there is no such suitable database index, a warning is given when the matchcode ID is activated. Two cases are distinguished here:

  • If the matchcode view on the database (taking into consideration the selection condition) contains fewer than 1000 data records, you need not create an index.
  • If the matchcode view on the database contains considerably more than 1000 records, you should create an index.

The first position of the index for supporting the matchcode selection should contain the fields to be searched for with equality (client, language, or, more generally, all fields for which the Get Parameter flag, that is, the GP flag in the screen Maintain Matchcode ID (Fields), is set).

The index should have the following structure:

  • client field
  • fields for which the Get Parameter flag is set
  • field to be found

This index structure, however, does not always ensure that the index is used by the underlying database system for data selection. The database system optimizer determines which index is actually used. Therefore, you must ensure that the secondary index you create is better than the primary index of the corresponding table created by the system.

You can check whether the index you created is used to support the matchcode selection as follows:

  1. In the maintenance screen for the attributes of the matchcode ID, choose Utilities ® Explain plan.

    A dialog box appears in which you can enter a search string for this ID.

  2. Choose Continue. The search string is committed.

    The explain plan for the search string is now displayed. The explain plan shows the structure of the SELECT statement used for the search string and how the SELECT statement is processed in the database. In particular, it displays the indexes used for reading the data from the base tables of the matchcode ID.

  3. Analyze the explain plan and, if necessary, create indexes for the tables that did not have a suitable index for the access.

The structure of the explain plan depends on the database system used. Further information about the explain plan can be found in the documentation on your database system.