CREATE INDEX
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE INDEX statement creates an index on an existing table. When the WITH STRUCTURE clause is omitted, it creates a primary (clustered) index. When WITH STRUCTURE=VECTORWISE_SI is used, it creates a secondary index on additional columns.
Note: Vertical (column-level) partitioning is not supported for indexes.
This statement has the following format:
CREATE INDEX [schema.]index_name ON
[schema.]table_name (column_name {, column_name})
WITH-clause
index_name
Defines the name of the index. It must be a valid object name.
table_name
Specifies the table on which the index is to be created.
column_name
Specifies a list of columns from the table to be included in the index.
WITH-clause
Specifies WITH clause options:
WITH STRUCTURE=
Specifies the index structure:
VECTORWISE_IX
(Default) Creates a primary (clustered) index. Only one primary index per table is allowed.
VECTORWISE_SI
Creates a secondary index on additional columns. Can also be specified as VWSI.
Note: Secondary indexes are not supported for partitioned tables.
CREATE INDEX Examples
1. Create a primary index named l_idx on the l_shipdate column of the lineitem table:
CREATE INDEX l_idx ON lineitem(l_shipdate);
2. Create a primary index named o_order_date_idx on the order_date column of the orders table:
CREATE INDEX o_order_date_idx ON orders(order_date);
3. Create a secondary index named si_supplier on the s_name column of the supplier table:
CREATE INDEX supplier_sidx ON supplier (s_name) WITH STRUCTURE=VECTORWISE_SI;
4. Create a secondary index named si_cars on certain columns of the cars table:
CREATE INDEX cars_sidx ON cars (model_id, body_type, color, year_produced) WITH STRUCTURE=VECTORWISE_SI;