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, except columns declared using a LOB data type or a structured user-defined type.
Columns in the
PRIMARY KEY, the columns of a
FOREIGN KEYand columns defined as
UNIQUEare 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.
If, for instance, you want to know which products were released on a specific date, Mimer SQL would have to search successively through the entire
ITEMStable to find all items that matched the date you specified. If, however, you create a secondary index on release date, Mimer SQL would locate that date directly in the secondary index, which would save time.
Secondary indexes can improve the efficiency of data retrieval; but introduces an overhead for write operations (
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 ITM_RELEASE_DATE on the RELEASE_DATE column in the ITEMS table:CREATE INDEX itm_release_date ON items(release_date);
Primary key columns may also be included in a secondary index. If a table has the primary key columns
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:
ABC. In addition, you could create secondary indexes on columns
An index may also be defined as
UNIQUE, which means that the index value may only occur once in the table. (For this purpose,
NULLis treated as equal to
NULL). However, it is preferable to use unique constraints.
Create a UNIQUE secondary index called ITM_EAN_CODE on the EAN_CODE column in the ITEMS table:CREATE UNIQUE INDEX itm_ean_code ON ITEMS(ean_code);
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. I.e. the following two indexes are compatible, and only one of them is required.CREATE INDEX idx_asc ON t1 (c1 ASC) CREATE INDEX idx_desc ON t1 (c1 DESC)
In some cases specifying the sort order makes sense. For example when ordering the result set by mixed orders, e.g:SELECT * FROM t1 ORDER BY c1 ASC, c2 DESC;
In this case the index below is appropriate:CREATE INDEX idx_mix ON t1 (c1 ASC, c2 DESC);
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40