Rules for Defining Primary Indexes

Rules for Defining primary indexes

The primary indexes for a table should represent the data values most used by the SQL to access the data for the table. Careful selection of the primary index is one of the most important steps in creating a table. Defining primary indexes should follow the following rules:

• A primary index should be defined to provide a nearly uniform distribution of rows among the AMPs, the more unique the index, the more even the distribution of rows and the better space utilization.
• The index should be defined on as few columns as possible.
• Primary index can be either Unique or non-unique. A unique index must have a unique value in the corresponding fields of every row;  a non-unique index permits the insertion of duplicate field values. The unique primary index is more efficient.
• Once created, the primary index cannot be dropped or modified, the index must be changed by recreating the table.

If a primary index is not defined in the CREATE TABLE statement through an explicit declaration of a PRIMARY INDEX, the default is to use one of the following:

• PRIMARY key
• First UNIQUE constraint
• First column

The primary index values are stored in an integral part of the primary table. It should be based on the set selection most frequently used to access rows from a table and on the uniqueness of the value.