CREATE INDEX Statement

Creates an index on a specified table, or pair of tables. Once an index is created, it is never referenced in an SQL statement again except to delete it using the DROP INDEX statement.

Quick Links:

Go to Parameters

Go to Examples

Go to Usage

Go to Standards

Go to Permissions

Syntax

CREATE [ UNIQUE ] [ index-type ] INDEX [ IF NOT EXISTS ] index-name
   …ON [ owner.]table-name
   … ( column-name [ , column-name ] …)
   …[ { IN | ON } dbspace-name ]
   …[ NOTIFY integer ]
   …[ DELIMITED BY 'separators-string‘ ]
   …[ LIMIT maxwordsize-integer ]

index-type
   { CMP | HG | HNG | LF | WD | DATE | TIME | DTTM }

Parameters

(back to top)

index-type – for columns in SAP Sybase IQ tables, you can specify an index-type of HG (High_Group), HNG (High_Non_Group), LF (Low_Fast), WD (Word), DATE, TIME, or DTTM (Datetime). If you do not specify an index-type, an HG index is created by default.
To create an index on the relationship between two columns in an IQ main store table, you can specify an index-type of CMP (Compare). Columns must be of identical data type, precision and scale. For a CHAR, VARCHAR, BINARY or VARBINARY column, precision means that both columns have the same width.
For maximum query speed, the correct type of index for a column depends on:
- The number of unique values in the column
- How the column is going to be used in queries
- The amount of disk space available
You can specify multiple indexes on a column of an IQ main store table, but these must be of different index types. CREATE INDEX does not let you add a duplicate index type. SAP Sybase IQ chooses the fastest index available for the current query or portion of the query. However, each additional index type might significantly add to the space requirements of that table.

column-name – specifies the name of the column to be indexed. A column name is an identifier preceded by an optional correlation name. (A correlation name is usually a table name. For more information on correlation names, see FROM Clause.) If a column name has characters other than letters, digits, and underscore, enclose it in quotation marks (“”).
If you omit the UNIQUE clause, you can specify only an HG index. Foreign keys require nonunique indexes and composite foreign keys require nonunique composite HG indexes. The multicolumn composite key for both unique and nonunique HG indexes has a maximum width of 5300 bytes. CHAR or VARCHAR data cannot be more than 255 bytes when it is part of a composite key or single-column HG, LF, HNG, DATE, TIME, or DTTM indexes.

UNIQUE – ensures that no two rows in the table have identical values in all the columns in the index. Each index key must be unique or contain a NULL in at least one column. You can create unique HG indexes with more than one column, but you cannot create multicolumn indexes using other index types. You cannot specify UNIQUE with the CMP, HNG, WD, DATE, TIME, or DTTM index types.
SAP Sybase IQ allows the use of NULL in data values on a user created unique multicolumn HG index, if the column definition allows for NULL values and a constraint (primary key or unique) is not being enforced. See “Multicolumn indexes” in Notes for more information.

IF NOT EXISTS – if the named object already exists, no changes are made and an error is not returned.

IN – specifies index placement. If you omit the IN clause, the index is created in the dbspace where the table is created. An index is always placed in the same type of dbspace (IQ store or temporary store) as its table. When you load the index, the data is spread across any database files of that type with room available. SAP Sybase IQ ensures that any dbspace-name you specify is appropriate for the index. If you try to specify IQ_SYSTEM_MAIN or other main dbspaces for indexes on temporary tables, or vice versa, you receive an error. Dbspace names are always case-insensitive, regardless of the CREATE DATABASE...CASE IGNORE or CASE RESPECT specification.

DELIMITED BY – specifies separators to use in parsing a column string into the words to be stored in the WD index of that column. If you omit this clause or specify the value as an empty string, SAP Sybase IQ uses the default set of separators. The default set of separators is designed for the default collation order (ISO-BINENG). It includes all 7-bit ASCII characters that are not 7-bit ASCII alphanumeric characters, except for the hyphen and the single quotation mark. The hyphen and the single quotation mark are part of words by default. There are 64 separators in the default separator set. For example, if the column value is this string:
```
The cat is on the mat
```
and the database was created with the CASE IGNORE setting using default separators, these words are stored in the WD index from this string:
```
cat is mat on the
```
If you specify multiple DELIMITED BY and LIMIT clauses, no error is returned, but only the last clause of each type is used.

separators-string – must be a sequence of 0 or more characters in the collation order used when the database was created. Each character in the separators string is treated as a separator. If there are no characters in the separators string, the default set of separators is used. (Each separator must be a single character in the collation sequence being used.) There cannot be more than 256 characters (separators) in the separators string.
To specify tab as a delimiter, you can either type a <TAB> character within the separator string, or use the hexadecimal ASCII code of the tab character, \x09. “\t” specifies two separators, \ and the letter t. To specify newline as a delimiter, you can type a <RETURN> character or the hexadecimal ASCII code \x0a.

For example, the clause DELIMITED BY ' :;.\/t' specifies these seven separators: space : ; . \ / t

Tab and Newline as Delimiters
Delimiter Separator Sstring for the DELIMITED BY Clause

tab ' ' (type <TAB>)or
'\x09'

newline ' ' (type <RETURN>) or '\x0a'

Tab and Newline as Delimiters
Delimiter	Separator Sstring for the DELIMITED BY Clause
tab	`' '` (type <TAB>)or `'\x09'`
newline	`' '` (type <RETURN>) or `'\x0a'`

LIMIT – can be used for the creation of the WD index only. Specifies the maximum word length that is permitted in the WD index. Longer words found during parsing causes an error. The default is 255 bytes. The minimum permitted value is 1 and the maximum permitted value is 255. If the maximum word length specified in the CREATE INDEX statement or determined by default exceeds the column width, the used maximum word length is silently reduced to the column width. Using a lower maximum permitted word length allows insertions, deletions, and updates to use less space and time. The empty word (two adjacent separators) is silently ignored. After a WD index is created, any insertions into its column are parsed using the separators and maximum word size determined at create time. These separators and maximum word size cannot be changed after the index is created.

NOTIFY – gives notification messages after n records are successfully added for the index. The messages are sent to the standard output device. A message contains information about memory usage, database space, and how many buffers are in use. The default is 100,000 records. To turn off NOTIFY, set it to 0.

Examples

(back to top)

Example 1 – creates a Compare index on the projected_earnings and current_earnings columns. These columns are decimal columns with identical precision and scale.
```
CREATE CMP INDEX proj_curr_cmp
ON sales_data
( projected_earnings, current_earnings )
```
Example 2 – creates a High_Group index on the ID column of the SalesOrderItems table. The data pages for this index are allocated from dbspace Dsp5.
```
CREATE HG INDEX id_hg
ON SalesOrderItems
( ID ) IN Dsp5
```
Example 3 – creates a High_Group index on the SalesOrderItems table for the ProductID column:
```
CREATE HG INDEX item_prod_hg
ON Sales_OrderItems
( ProductID)
```
Example 4 – creates a Low_Fast index on the SalesOrderItems table for the same ProductID column without any notification messages:
```
CREATE LF INDEX item_prod
ON SalesOrderItems
( ProductID)
 NOTIFY 0
```
Example 5 – creates a WD index on the earnings_report table. Specify that the delimiters of strings are space, colon, semicolon, and period. Limit the length of the strings to 25.
```
CREATE WD INDEX earnings_wd
ON earnings_report_table(varchar)
DELIMITED BY ‘ :;.’
LIMIT 25
```
Example 6 – creates a DTTM index on the SalesOrders table for the OrderDate column:
```
CREATE DTTM INDEX order_dttm
ON SalesOrders
( OrderDate )
```

Usage

(back to top)

There is no way to specify the index owner in the CREATE INDEX statement. Indexes are automatically owned by the owner of the table on which they are defined. The index name must be unique for each owner.
Indexes cannot be created for views. The name of each index must be unique for a given table.
CREATE INDEX is prevented whenever the statement affects a table currently being modified by another connection. However, queries are allowed on a table that is also adding an index.
After a WD index is created, any insertions into its column are parsed using the separators, and maximum word size cannot be changed after the index is created. For CHAR columns, specify a space as at least one of the separators or use the default separator set. SAP Sybase IQ automatically pads CHAR columns to the maximum column width. If your column contains blanks in addition to the character data, queries on WD indexed data might return misleading results. For example, column CompanyName contains two words delimited by a separator, but the second word is blank padded:
```
‘Concord’ ‘Farms					       ’
```
Suppose that a user entered this query:
```
SELECT COUNT(*)FROM Customers WHERE CompanyName contains (‘Farms’)
```
The parser determines that the string contains:
```
‘Farms           ’
```
instead of:
```
‘Farms’
```
and returns 0 instead of 1. You can avoid this problem by using VARCHAR instead of CHAR columns.
Data types:
- You cannot use CREATE INDEX to create an index on a column with BIT data.
- Only the default index, CMP index, or WD index can be created on CHAR and VARCHAR data with more than 255 bytes.
- Only the default and WD index types can be created on LONG VARCHAR data.
- Only the default index, CMP index, and TEXT index types can be created on BINARY and VARBINARY data with more than 255 bytes.
- An HNG index or a CMP index cannot be created on a column with FLOAT, REAL, or DOUBLE data.
- A TIME index can be created only on a column having the data type TIME.
- A DATE index can be created only on a column having the data type DATE.
- A DTTM index can be created only on a column having the data type DATETIME or TIMESTAMP.
You can create a unique or nonunique HG index with more than one column. SAP Sybase IQ implicitly creates a nonunique HG index on a set of columns that makes up a foreign key.
HG and CMP are the only types of indexes that can have multiple columns. You cannot create a unique HNG or LF index with more than one column, and you cannot create a DATE, TIME, or DTTM index with more than one column.

The maximum width of a multicolumn concatenated key is 5KB (5300 bytes). The number of columns allowed depends on how many columns can fit into 5KB. CHAR or VARCHAR data greater than 255 bytes are not allowed as part of a composite key in single-column HG, LF, HNG, DATE, TIME, or DTTM indexes.

An INSERT on a multicolumn index must include all columns of the index.

Queries with a single column in the ORDER BY clause run faster using multicolumn HG indexes. For example:
```
SELECT abs (x) from t1
ORDER BY x
```
In the above example, the HG index vertically projects x in sorted order.

To enhance query performance, use multicolumn HG indexes to run ORDER BY operations on more than one column (that can also include ROWID) in the SELECT or ORDER BY clause with these conditions:
- All projected columns, plus all ordering columns (except ROWID), exist within the index
- The ordering keys match the leading HG columns, in order
If more than one multicolumn HG index satisfies these conditions, the index with the lowest distinct counts is used.

If a query has an ORDER BY clause, and the ORDER BY column list is a prefix of a multicolumn index where all columns referenced in the SELECT list are present in a multicolumn index, then the multicolumn index performs vertical projection; for example:
```
SELECT x,z,y FROM T 
ORDER BY x,y
```
If expressions exist on base columns in the SELECT list, and all the columns referenced in all the expressions are present in the multicolumn index, then the query will use a multicolumn index; for example:
```
SELECT power(x,2), x+y, sin(z) FROM T 
ORDER BY x,y
```
In addition to the two previous examples, if the ROWID() function is in the SELECT list expressions, multicolumn indexes will be used. For example:
```
SELECT rowid()+x, z FROM T 
ORDER BY x,y,z
```
In addition to the three previous examples, if ROWID() is present at the end of an ORDER BY list, and if the columns of that list—except for ROWID()—use multicolumn indexes in the exact order, multicolumn indexes will be used for the query. For example:
```
SELECT z,y FROM T 
ORDER BY x,y,z,ROWID()
```
SAP Sybase IQ allows the use of NULL in data values on a user created unique multicolumn HG index, if the column definition allows for NULL values and a constraint (primary key or unique) is not being enforced. The rules for this feature are as follows:
- A NULL is treated as an undefined value.
- Multiple rows with NULL values in a unique index column or columns are allowed.
  1. In a single column index, multiple rows with a NULL value in an index column are allowed.
  2. In a multicolumn index, multiple rows with a NULL value in index column or columns are allowed, as long as non-NULL values in the rest of the columns guarantee uniqueness in that index.
  3. In a multicolumn index, multiple rows with NULL values in all columns participating in the index are allowed.
These examples illustrate these rules. Given the table table1:
```
CREATE TABLE table1
(c1 INT NULL, c2 INT NULL, c3 INT NOT NULL);
```
Create a unique single column HG index on a column that allows NULLs:
```
CREATE UNIQUE HG INDEX c1_hg1 ON table1 (c1);
```
According to rule 1 above, you can insert a NULL value into an index column in multiple rows:
```
INSERT INTO table1(c1,c2,c3) VALUES (NULL,1,1);
INSERT INTO table1(c1,c2,c3) VALUES (NULL,2,2);
```
Create a unique multicolumn HG index on a columns that allows NULLs:
```
CREATE UNIQUE HG INDEX c1c2_hg2 ON table1(c1,c2);
```
According to rule 2 above, you must guarantee uniqueness in the index. The following INSERT does not succeed, since the multicolumn index c1c2_hg2 on row 1 and row 3 has the same value:
```
INSERT INTO table1(c1,c2,c3) VALUES (NULL,1,3);
```
These INSERT operations are successful, however, according to rules 1 and 3:
```
INSERT INTO table1(c1,c2,c3) VALUES (NULL,NULL,3);
INSERT INTO table1(c1,c2,c3) VALUES (NULL,NULL,4);
```
Uniqueness is preserved in the multicolumn index.

This UPDATE operation is successful, as rule 3 allows multiple rows with NULL values in all columns in the multicolumn index:
```
UPDATE table1 SET c2=NULL WHERE c3=1
```
When a multicolumn HG index is governed by a unique constraint, a NULL value is not allowed in any column participating in the index.
You can use the BEGIN PARALLEL IQ … END PARALLEL IQ statement to group CREATE INDEX statements on multiple IQ main store tables, so that they execute as though they are a single DDL statement. See BEGIN PARALLEL IQ … END PARALLEL IQ Statement for more information.

Warning! Using the CREATE INDEX command on a local temporary table containing uncommitted data fails and generates the error message Local temporary table, <tablename>, must be committed in order to create an index. Commit the data in the local temporary table before creating an index.

Side Effects

Automatic commit

Standards

(back to top)

SQL—Vendor extension to ISO/ANSI SQL grammar.
SAP Sybase Database product—Adaptive Server has a more complex CREATE INDEX statement than SAP Sybase IQ. While the Adaptive Server syntax is permitted in SAP Sybase IQ, some clauses and keywords are ignored. For the full syntax of the Adaptive Server CREATE INDEX statement, see the Adaptive Server Reference Manual, Volume 2: Commands.

Adaptive Server indexes can be either clustered or nonclustered. A clustered index almost always retrieves data faster than a nonclustered index. Only one clustered index is permitted per table.

SAP Sybase IQ does not support clustered indexes. The CLUSTERED and NONCLUSTERED keywords are allowed by SQL Anywhere, but are ignored by SAP Sybase IQ. If no index-type is specified, SAP Sybase IQ creates an HG index on the specified column(s).

SAP Sybase IQ does not permit the DESC keyword.

Index names must be unique on a given table for both SAP Sybase IQ and Adaptive Server.

Permissions

(back to top)

Requires CREATE privilege on the dbspace where the index is being created. Also requires one of::

CREATE ANY INDEX system privilege.
CREATE ANY OBJECT system privilege.
REFERENCES privilege on the underlying table of the index.
You own the underlying table of the index.