You use CREATE INDEX to generate an index for a base table. SESAM/SQL can use the index to evaluate constraints on one or more columns of the index without accessing the base table or to output the rows in the table in the order of the values in the index column(s).
The restrictions and special considerations that apply to CALL DML tables are described in the section "Special considerations for CALL DML tables".
The current authorization identifier must own the schema to which the base table belongs.
If you specify the space for the index, the current authorization identifier must own the space.
CREATE INDEX index_definition ,...ON TABLE table [USING SPACE space ]
index_definition ::= index ({ column [LENGTH length ]},...)
index_definition
Definition of one or more indexes
If you create an index for only one column, the column may not be longer than 256 characters. If you create an index involving several columns, the sum of the column lengths plus the total number of columns cannot exceed 256.
index
Name of the new index. The unqualified index name must be unique within the schema. You can qualify the index name with a database and schema name. The database and schema name must be the same as the database and schema name of the base table for which you are creating the index.
If you use the CREATE INDEX statement in a CREATE SCHEMA statement, you can only qualify the index name with the database and schema name from the CREATE SCHEMA statement.
column
Name of the column in the base table you want to index.
A column cannot occur more than once in an index. You can create an index that applies to several columns (compound index). In this case, the index cannot apply to multiple columns.
LENGTH length
Indicates the length up to which the column is to be indexed. length must be an unsigned integer between 1 and the length of the column. You can only limit the length if the column is of the following data type: CHAR, VARCHAR, NCHAR and NVARCHAR or data types from SESAM up to V12.
LENGTH length omitted:
The column in its entirety in bytes is indexed.
ON TABLE table
Name of the base table you are indexing.
If you qualify the table name with a database and schema name, this must be the same as the database and schema name of the index.
If you use the CREATE INDEX statement in a CREATE SCHEMA statement, you can only qualify the table name with the database and schema name from the CREATE SCHEMA statement.
USING SPACE space
Name of the space in which the index is to be stored.
You can qualify the space name with the database name. This database name must be the same as the database name of the base table.
The space must already be defined for the database to which the table belongs. The current authorization identifier must own the space.
USING SPACE space omitted:
The index is stored in the space for the base table. In the case of a partitioned table, the index is stored in the space for the first partition.
Special considerations for CALL DML tables
The CREATE INDEX statement for CALL DML tables must take the following restrictions and special considerations into account:
Every index can only apply to one column.
Each column can only occur once in an index.
You can only specify the name of the primary key constraint of a database with a compound key as the column name in the index. This means that the primary key is indexed.
Indexes and integrity constraints
If you define a UNIQUE integrity constraint for a table, the columns specified in the UNIQUE constraint are implicitly indexed. If you explicitly define an index with CREATE INDEX that applies to the same columns, the implicitly defined index is deleted. The explicit index is then also used for the integrity constraint.
Examples
The example below creates a compound index for the columns CUST_NUM and COMPANY in the CUSTOMERS table. The COMPANY column is included in the index to a length of 10 characters. Store the index in the INDEXSPACE space.
CREATE INDEX cust_ind (cust_num,company LENGTH 10)
ON TABLE customers USING SPACE indexspace
In the CREATE INDEX statement, the index NAT_CUST_IND is defined for the NAT_CUST_NUM and NAT_COMPANY columns of the NAT_CUSTOMERS table. The NAT_COMPANY column has the national data type NCHAR. The first 5 characters of values in the NAT_COMPANY column are included when the index is created (1 character = 2 bytes). The index is to be created on the space with the name NAT_INDEXSPACE.
CREATE INDEX nat_cust_ind(nat_cust_num, nat_company LENGTH 10)
ON TABLE nat_customers USING SPACE nat_indexspace
See also
DROP INDEX