Creating an Index
Overview
To create an index on one or more columns of a table,
use the CREATE INDEX statement.
|
General form, CREATE
INDEX statement:
CREATE <UNIQUE> INDEX index-name
ON table-name (column-name-1<,
...column-name-n>);
UNIQUE
is a keyword that specifies
that all values of the column(s) specified in the statement must be
unique.
index-name
specifies the name
of the index to be created. If you are creating an index on one column
only, then index-name must
be the same as column-name-1.
If you are creating an index on more than one column, then index-name cannot
be the same as the name of any existing column or index in the table.
table-name
specifies the name
of the table on which the index is created.
column-name
specifies a column
to be indexed. Columns can be specified in any order. However, column
order is important for data retrieval. The first-named column is the
primary key, the second-named column is the secondary key, and so
on.
|
Tip
When creating a composite
index, specify the columns in the same order as you would specify
them in an ORDER BY clause.
Tip
You can achieve improved
index performance if you create the index on a pre-sorted table.
SAS maintains indexes
for all changes to the table, whether the changes originate from PROC
SQL or some other source, as long as the entire table is not re-created.
If you alter a column's definition or update its values, then SAS
updates the indexes also. However, if a key column in a table is dropped
(deleted), then the index on that column is also dropped.
Creating Multiple Indexes
You cannot
create multiple simple indexes that are based on the same column or
multiple composite indexes that are based on the same set of columns.
Although it is possible to create both a simple index and a composite
index on the same column, it is usually not advantageous to do this.
If a simple index is defined on a column and that column is also the
primary key in a composite index, PROC SQL uses the composite index
in processing a query that references that column.
You can create multiple
indexes on the same table, but you must use a separate CREATE INDEX
statement for each index that you want to create.
Example: Creating a Simple Index
The following
PROC SQL step uses the CREATE INDEX statement to create a simple,
unique index that is based on the column EmpID in the temporary table
Work.Payrollmaster. (Work.Payrollmaster is a duplicate of the table
Sasuser.Payrollmaster.)
proc sql; create unique index EmpID on work.payrollmaster(empid);
The specified index
name (EmpID) must be the same as the name of the key column.
When this step is submitted,
the SAS log displays the following message.
Example: Creating a Composite, Unique Index
The following PROC SQL step uses the CREATE INDEX statement
to create the composite, unique index daily on the columns FlightNumber
and Date:
proc sql; create unique index daily on work.marchflights(flightnumber,date);
When this step is submitted,
the SAS log displays the following message.
Note: The note in the SAS log displays
the index name exactly as you specified it. In this example, the index
name daily was specified in lowercase. In the previous example, the
index name EmpID was specified in mixed case. However, the use of
uppercase and lowercase for index names is not significant because
SAS recognizes index names regardless of how they are formatted in
code.
If the set of key columns
FlightNumber and Date had duplicate values, the index would not be
created. Instead, the SAS log would display a message like the following.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.