Creating an index considering selectivity

From: Andrea Turcatti <turcatti_at_prism.cc.purdue.edu>
Date: 1995/08/01
Message-ID: <3vlgb0$lls_at_mozo.cc.purdue.edu>#1/1

The order in which columns are named in the CREATE INDEX command need not correspond to the order in which they appear in the table. However, the order of columns in the CREATE INDEX statement is significant because query performance can be affected by the order chosen.
In case of a composite index, an index that is made up of more than one column, you should put the most selective column first, that is , the column with the most different values. If a composite index is to be used by queries based on multiple column values, ordering these columns from most selective to least selective in the CREATE INDEX statement best improves query performance.

Note: A SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of the index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX. i.e.:

        CREATE INDEX comp_ind
              ON tableA(x,y,z);

Leading portion of the index: x, xy, xyz Not leading portion of the index: xz, yz, z Received on Tue Aug 01 1995 - 00:00:00 CEST