CREATE INDEX statement

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 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.

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.

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.

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()

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.