Creating Secondary Indexes

tsinglee發表於2007-11-22

引用自:http://developer.mimer.com/documentation/html_92/Mimer_SQL_Mobile_DocSet/Defining_Database9.html

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.

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.

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 ITEMS table 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 (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.

[@more@]

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10599713/viewspace-983309/,如需轉載,請註明出處,否則將追究法律責任。

相關文章