An index
stores both the values of a table's columns and a system of directions
that enable access to rows in that table by index value. Defining
an index on a column or set of columns enables SAS, under certain
circumstances, to locate rows in a table more quickly and efficiently.
Indexes enable PROC SQL to execute the following classes of queries
more efficiently:
-
comparisons against a column that
is indexed
-
an IN subquery where the column
in the inner subquery is indexed
-
correlated subqueries, where the
column being compared with the correlated reference is indexed
-
join-queries, where the join-expression
is an equals comparison and all the columns in the join-expression
are indexed in one of the tables being joined.
SAS maintains indexes
for all changes to the table, whether the changes originate from PROC
SQL or from some other source. Therefore, if you alter a column's
definition or update its values, then the same index continues to
be defined for it. However, if an indexed column in a table is dropped,
then the index on it is also dropped.
You can
create simple or composite indexes. A
simple index is
created on one column in a table. A simple index must have the same
name as that column. A
composite index is
one index name that is defined for two or more columns. The columns
can be specified in any order, and they can have different data types.
A composite index name cannot match the name of any column in the
table. If you drop a composite index, then the index is dropped for
all the columns named in that composite index.