Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Creating Secondary Indexes


A secondary index is automatically used during searching when it improves the efficiency of the search.

Secondary indexes are maintained by the system and are invisible to the user.

Any column(s) may be specified as a secondary index.

Columns in the PRIMARY KEY, the columns of a FOREIGN KEY and columns defined as UNIQUE are automatically indexed, (in the order in which they are defined in the key), and therefore creation of an index on these columns will not improve performance.

Secondary index tables are purely for Mimer SQL's internal use - you create the index, and Mimer SQL handles the rest. Index names can be made up of a maximum of 128 characters.

If, for instance, you want to know which room a certain person is staying in at a hotel, Mimer SQL would have to search successively through the customer reference numbers and the names corresponding to each in order to find the information you want. If, however, you create a secondary index on guest names, Mimer SQL would search for the name of that person directly in the secondary index, which would save time.

Secondary indexes can improve the efficiency of data retrieval; but does introduce an overhead for write operations (UPDATE, INSERT, DELETE). In general, you should create indexes only for columns that are frequently searched.

Indexes cannot be created directly on columns in views. However, since searching in a view is actually implemented as searching in the base table, an index on the base table will also be used in view operations.

Examples of Secondary Index

Create a secondary index called NAME on the GUEST_LNAME column in the BOOK_GUEST table:
 CREATE INDEX NAME
 ON BOOK_GUEST (GUEST_LNAME);
 

Primary key columns may also be included in a secondary index. If a table has the primary key 'A,B,C', the primary index would cover all three columns of the primary key.

The following combinations of the columns in the primary key are automatically indexed: 'A', 'A,B', and 'A,B,C'. In addition, you could create secondary indexes on columns B, C, BC, AC etc.

An index may also be defined as UNIQUE, which means that the index value may only occur once in the table. (For this purpose, NULL is treated as equal to NULL).

Create a UNIQUE secondary index called OCCUPANCY on the GUEST_LNAME and ROOMNO columns in the BOOK_GUEST table:
 CREATE UNIQUE INDEX OCCUPANCY
 ON BOOK_GUEST (GUEST_LNAME, ROOMNO);

Sorting Indexes

The sorting order for indexes may be defined as ascending or descending. However, this makes no difference to the efficiency of the index, since Mimer SQL searches indexes forwards or backwards depending on the circumstances.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX