Primary Keys and Indexes
Rows in a base table are uniquely identified by the value of the primary key defined for the table. The primary key for a table is composed of the values of one or more columns. A table cannot contain two rows with the same primary key value. (If the primary key contains more than one column, the key value is the combined value of all the columns in the key. Individual columns in the key may contain duplicate values as long as the whole key value is unique).
Other columns may also be defined as UNIQUE. A unique column is also a key, because it may not contain duplicate values, and need not necessarily be part of the primary key.
The columns of the primary key may not contain NULL (this is one of the requirements of a strictly relational database).
Values in primary key columns can be updated if the table involved is stored in a databank with the TRANS or LOG option.
Primary key and unique columns are automatically indexed to facilitate effective information retrieval.
Other columns or combinations of columns may be defined as a secondary index to improve performance in data retrieval. Secondary indexes are defined on a table after it has been created (using the CREATE INDEX statement).
A secondary index may be useful when, for example, a search is regularly performed on a non-keyed column in a table with many rows, then defining an index on the column may speed up the search. The search result is not affected by the index but the speed of the search is optimized.
It should be noted, however, that indexes create an overhead for update, delete and insert operations because the index must also be updated. Indexes are internal structures which cannot be explicitly accessed by the user once created.
There is no guarantee that the presence of an index will actually improve performance because the decision to use it or not is made by the internal query optimization process.
SQL queries are automatically optimized when they are internally prepared for execution. The optimization process determines the most effective way to execute the query and in some cases optimal query execution may not actually involve using an index.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40