Creates an index on a specified table, or pair of tables.
CREATE [ UNIQUE ] [ index-type ] INDEX index-name
... ON [ owner.]table-name
... ( column-name [, column-name ] ...)
... [ { IN | ON } dbspace-name ]
... [ NOTIFY integer ]
... [ DELIMITED BY ‘separators-string ‘ ]
... [ LIMIT maxwordsize-integer ]
{ CMP | HG | HNG | LF | WD | DATE | TIME | DTTM }
Create 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 )
Create a High_Group index on the sales_order_items table for the product ID column.
CREATE HG INDEX item_prod_hg ON sales_order_items ( prod_id )
Create a Low_Fast index on the sales_order_items table for the same product ID column without any notification messages.
CREATE LF INDEX item_prod ON sales_order_items ( prod_id ) NOTIFY 0
Create 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
Create a DTTM index on the sales_order table for the order_date column.
CREATE DTTM INDEX order_dttm ON sales_order ( order_date )
The CREATE INDEX statement creates an index on the specified column of the named table. Once an index is created, it is never referenced in a SQL statement again except to delete it using the DROP INDEX statement.
For columns in 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 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 that column is going to be used in queries
The amount of disk space available
The Sybase IQ System Administration Guide describes the index types in detail and tells how to determine the appropriate index types for your data.
You can specify multiple indexes on a column of an IQ table, but these must be of different index types. The CREATE INDEX command does not allow you to add a duplicate index type. Sybase IQ will choose the fastest index available for the current query or portion of the query. However, each additional index type may 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 ("").
When you omit the UNIQUE keyword, you can only specify an HG index. Foreign keys require non-unique HG indexes and composite foreign keys require non-unique composite HG indexes. The multicolumn composite key for both unique and non-unique 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 attribute The UNIQUE attribute ensures that there will not be two rows in the table with 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 the UNIQUE attribute with the CMP, HNG, WD, DATE, TIME, or DTTM index types.
Index placement 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. While the CREATE INDEX command lets you specify the dbspace-name IQ_SYSTEM_TEMP or IQ_SYSTEM_MAIN, this option has no real effect for IQ indexes. IQ ensures that any dbspace-name you specify is appropriate for the index. If you try to specify IQ_SYSTEM_MAIN for indexes on temporary tables, or vice versa, you receive an error. Dbspace names are case sensitive for databases created with CASE RESPECT.
DELIMITED BY clause Specifies separators to use in parsing a column string into the words to be stored in that column’s WD index. If this clause is omitted or the value specified is the empty string, then 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 the string
The cat is on the mat
and the database was created with the CASE IGNORE setting using default separators, the following words are stored in the WD index from this string:
cat is mat on the
If multiple DELIMITED BY and LIMIT clauses are specified, no error is returned, but only the last clause of each type is used.
separators-string The 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 may not 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. Note that \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
For these delimiters... |
Use this separators-string in the DELIMITED BY clause |
|---|---|
tab |
|
newline |
|
LIMIT clause May be used for the creation of the WD index only. Specifies the maximum word length that will be permitted in the WD index. Longer words found during parsing will cause 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. Note that the empty word (two adjacent separators) is silently ignored. After a WD index is created, any insertions into its column will be 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 clause 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.
Index ownership There is no way of specifying 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.
Index name The name of each index must be unique for a given table.
Exclusive table use 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.
CHAR columns After a WD index is created, any insertions into its column will be parsed using the separators and maximum word size cannot be changed after the index is created.
For CHAR columns, Sybase recommends that you specify a space as at least one of the separators or use the default separator set. 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 may return misleading results. For example, column company_name contains two words delimited by a separator, but the second word is blank padded:
‘Concord’ ‘Farms ’
Suppose that a user entered the following query:
SELECT COUNT(*)FROM customers WHERE company_name 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 index and CMP index can be created on VARBINARY data with more than 255 bytes. In addition, you cannot create an HNG index or a CMP index on a column with FLOAT, REAL, or DOUBLE data. A TIME index can only be created on a column having the data type TIME. A DATE index can only be created on a column having the data type DATE. A DTTM index can only be created on a column having the data type DATETIME or TIMESTAMP.
Multicolumn indexes You can create a unique or non-unique HG index with more than one column. (Sybase IQ implicitly creates a non-unique HG index on a set of columns that make 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. 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 1KB. CHAR or VARCHAR data greater than 255 bytes is not allowed as part of a composite key in single-column HG, LF, HNG, DATE, TIME, or DTTM indexes.
Multicolumn indexes on base tables are not replicated in join indexes created using those base tables.
An INSERT on a multicolumn index must include all columns of the index.
Parallel index creation You can use the BEGIN PARALLEL IQ ... END PARALLEL IQ statement to group CREATE INDEX statements on multiple IQ tables, so that they execute as though they are a single DDL statement. See the BEGIN PARALLEL IQ ... END PARALLEL IQ statement for more information.
WARNING! Using the CREATE INDEX command on a local temporary table containing uncommitted data will fail and generate the following error message: “Local temporary table, <tablename>, must be committed in order to create an index.” Be sure to commit the data in the local temporary table before creating an index.
Automatic commit.
Sybase Adaptive Server Enterprise has a more complex CREATE INDEX statement than Sybase IQ. While the Adaptive Server Enterprise syntax is permitted in Sybase IQ, some clauses and keywords are ignored. For the full syntax of the Adaptive Server Enterprise CREATE INDEX statement, refer to the Adaptive Server Enterprise Reference Manual, Volume 2: Commands.
Adaptive Server Enterprise 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.
Sybase IQ does not support clustered indexes. The CLUSTERED and NONCLUSTERED keywords are allowed by SQL Anywhere, but no action is taken.
Sybase IQ does not permit the DESC keyword.
Sybase IQ also allows, by ignoring, the following keywords:
FILLFACTOR
IGNORE_DUP_KEY
SORTED_DATA
IGNORE_DUP_ROW
ALLOW_DUP_ROW
ON
Index names must be unique on a given table for both Sybase IQ and Adaptive Server Enterprise.
Must be the owner of the table or have DBA authority.
Chapter 6, “Using Sybase IQ Indexes” in the Sybase IQ System Administration Guide
