sp_iqrebuildindex Procedure

Rebuilds one or more indexes on a table with the original IQ UNIQUE value specified in the CREATE TABLE statement, or a new IQ UNIQUE value to change storage required and/or query performance.

To rebuild an index other than the default index, specify the index name.

Syntax

sp_iqrebuildindex (table_name, index_clause)

Permissions

You must have EXECUTE permission to run this procedure. You must have INSERT permission on a table to rebuild an index on that table.

Usage

Parameters
Parameter	Description
table_name	Partial or fully qualified table name on which the index rebuild process takes place. If the user both owns the table and executes the procedure, a partially qualified name may be used; otherwise, the table name must be fully qualified.
index_clause	One or more of the following strings, separated by spaces: column `column_name` `[count]` index `index_name` Each `column_name` or `index_name` must refer to a column or index on the specified table. If you specify a `column_name` or `index_name` multiple times, the procedure returns an error and no index is rebuilt. The `count` is a nonnegative number that represents the IQ UNIQUE value. In a CREATE TABLE statement, IQ UNIQUE (count) approximates how many distinct values can be in a given column. The number of distinct values affects query speed and storage requirements.

You must specify the keywords column and index. These keywords are not case-sensitive.

Note: This procedure does not support TEXT indexes. To rebuild a TEXT index you must drop and re-create the index.

Description

If you specify a column name, the procedure rebuilds the default index for that column, and no index name is needed. Specifying the name of the default index assigned by Sybase IQ in addition to the column name in this situation returns an error. If you omit count after the column_name, value 0 (zero) is used as the default.

If the default index is a one-byte index, sp_iqrebuildindex always rebuilds it as a one-byte index no matter what IQ UNIQUE value the user specified.

For one-byte default indexes, if the specified value in column_name (count) is 0 or greater than 256, the column’s cardinality value is used to update the approx_unique_count column in SYS.SYSIQCOLUMN.

sp_iqrebuildindex rebuilds a WD index on a column of data type LONG VARCHAR (CLOB).

If the default index is a two-byte index, and the specified count is 0 or greater than 65536, the column’s cardinality value determines whether to rebuild the default into a one-byte or two-byte index, and that value is used to update the approx_unique_count column in SYS.SYSIQCOLUMN.

If you specify a nonzero IQ UNIQUE value, the default index is rebuilt as a one-byte, two-byte, or flat default index, with exceptions described above.

If you specify an IQ UNIQUE value of zero or no IQ UNIQUE value, the MINIMIZE_STORAGE option controls how the index is rebuilt:

If MINIMIZE_STORAGE option is set ON, the index is rebuilt as a one-byte default index first, and converted to two-byte or flat if necessary.
If MINIMIZE_STORAGE is set OFF, the index is rebuilt using the default for the data type.

Examples

Rebuilds the default index on column Surname:

sp_iqrebuildindex ‘emp1‘, ‘column dept_id‘

or:

call sp_iqrebuildindex (‘empl1‘, ‘column dept_id‘)

Creates a flat default index on column c1:

CREATE TABLE mytable (c1 int IQ UNIQUE 1000000000)

Converts the default one-byte index to a two-byte index:

sp_iqrebuildindex ‘mytable‘, ‘column c1 1024‘

or:

call sp_iqrebuildindex (‘mytable‘, ‘column c1 1024‘)

Note: Users can expect to see a temporary performance drop when sp_iqrebuildindex runs on a large HG index.