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.
sp_iqrebuildindex (table_name, index_clause)
You must have INSERT permission on a table to rebuild an index on that table.
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. For details, see “Optimizing storage and query performance,” in Chapter 5, “Working with Database Objects” in the Sybase IQ System Administration Guide.
You must specify the keywords column and index. These keywords are not case sensitive.
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.
If the column has the data type VARCHAR or VARBINARY greater than 255 bytes, sp_iqrebuildindex will not rebuild a default index.
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. For more information, see “Sybase IQ index types” in Sybase IQ System Administration Guide.
“sp_iqindexfragmentation procedure”,“sp_iqrowdensity procedure”, and “SYSIQCOLUMN system table”
The following procedure rebuilds the default index on column emp_lname:
sp_iqrebuildindex ‘employee‘, ‘column emp_lname‘
or
call sp_iqrebuildindex (‘employee‘, ‘column emp_lname‘)
The following SQL statement creates a flat default index on column c1:
CREATE TABLE mytable (c1 int IQ UNIQUE 1000000000)
This procedure 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‘)
