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]
[dbspace dbspace_name]
index index_name
[dbspace dbspace_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 System Administration Guide: Volume 1.
You must specify the keywords column and index. The keyword dbspace is optional. These keywords are not case-sensitive.
Sybase IQ rebuilds the column or index in the same dbspace where the original resided unless you specify dbspace dbspace-name.
“sp_iqindexfragmentation procedure”, and “sp_iqrowdensity procedure”.
Chapter 6, “Using Sybase IQ Indexes” in System Administration Guide: Volume 1.
“FP_LOOKUP_SIZE option” and “MINIMIZE_STORAGE option” in Chapter 2, “Database Options” in Reference: Statements and Options.
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.
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. For more information, see “Sybase IQ index types” Chapter 7, “System Procedures” in the System Administration Guide: Volume 1.
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‘)
“sp_iqindexfragmentation procedure”, and “sp_iqrowdensity procedure”.
“FP_LOOKUP_SIZE option” and “MINIMIZE_STORAGE option” in Chapter 2, “Database Options” in Reference: Statements and Options.
Chapter 6, “Using Sybase IQ Indexes” in System Administration Guide: Volume 1.
