Definition:

An index can be considered a copy of a database table that has been reduced to certain fields. The copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search. The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can be read.

Types of Indexes:

a) Primary Index

b) Secondary Index

The primary index contains the key fields of the table and a pointer to the non key fields of the table. The primary index is created automatically when the table is created in the database.

The need for secondary index arises if the table is frequently accessed in a way that dos not take advantage of the sorting of the primary index for the access.

Secondary Index:

1) How to create a secondary Index –

Procedure…

1. In the maintenance screen of the table, choose Indexes.

If indexes already exist on the table, a list of these indexes is displayed. Choose .

2. In the next dialog box, enter the index ID and choose

The maintenance screen for indexes appears.

3. Enter an explanatory text in the field Short text.

You can then use the short text to find the index at a later time – for example through the Information System.

4. Select the table fields to be inserted in the index using the input help for the Field name column.

The order of the fields in the index is very important. See What to Keep in Mind for Secondary Indexes

5. If the values in the index fields already uniquely identify each record of the table, select unique index.

A unique index is always created in the database at activation because it also has a functional meaning (prevents double entries of the index fields).

6. If it is not a unique index, leave Non-unique index selected.

In this case you can use the radio buttons to define whether the index should be created for all database systems, for selected database systems or not at all in the database.

7. Select for selected database systems if the index should only be created for selected database systems.

Click on the arrow behind the radio buttons. A dialog box appears in which you can define up to 4 database systems with the input help. Select Selection list if the index should only be created on the given database systems. Select Exclusion list if the index should not be created on the given database systems. Choose.

8. Choose.

Result

The secondary index is automatically created on the database during activation if the corresponding table has already been created there and index creation was not excluded for the database system.

You can find information about the activation flow in the activation log, which you can call with Utilities ® Activation log. If errors occurred when activating the index, the activation log is automatically displayed.

2) How to Check if an Index is Used –

Procedure

1. Open a second session and choose System ® Utilities ® Performance trace.

The Trace Requests screen appears.

1. Select Trace on.

The SQL trace is activated for your user that is all the database operations under your user are recorded.

1. In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

1. In the second session, choose Trace off and then Trace list.

Result

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

3) What to Keep in mind for Secondary Indexes –

· How well an existing index supports data selection from a table largely depends on whether the data selected with the index represents the data that will ultimately be selected. This can best be shown using an example.

· An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:

· SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4.

· Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

· The order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

· Only those fields that significantly restrict the set of results in a selection make sense for an index.

· The following selection is frequently made on address file ADRTAB:

· SELECT * FROM ADRTAB WHERE TITEL = ‘Prof.’ AND NAME = X AND VORNAME = Y.

· The field TITLE would rarely restrict the records specified with NAME and FIRSTNAME in an index on NAME, FIRSTNAME and TITLE, since there are probably not many people with the same name and different titles. This would not make much sense in this index. An index on field TITLE alone would make sense for example if all professors are frequently selected.

· Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table.

· For this reason, tables in which entries are very frequently written generally should only have a few indexes.

· The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the optimizer used for the database system. You should therefore check if the index you created is also used for the selection (see How to check if an Index is used).).

· Creating an additional index could also have side effects on the performance. This is because an index that was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.

· The indexes on a table should therefore be as disjunctive as possible, that is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.