sp_iqrebuildindex Procedure

To rebuild an index other than the default FP index, specify the index name. sp_iqrebuildindex behavior is the same regardless of the FP_NBIT_IQ15_COMPATIBILITY setting.

Syntax

sp_iqrebuildindex (table_name, index_clause)

Note: A third-party reference document describes an unsupported sp_iqrebuildindex syntax. Please note that specifying the table name in the index clause results in an error:

sp_iqrebuildindex tb1, 'column tb1.c1'

Parameter

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:
columncolumn_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 non-negative 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.
MERGEALL and RETIER are keywords specific to HG index operations:
```
sp_iqrebuildindex(‘table name’, ' index index name [ MERGEALL | RETIER ] ’)
```
If MERGEALL or RETIER are omitted from an operation from an HG index , sp_iqrebuildindex truncates and reconstructs the entire HG index from the column data.
MERGEALL merges all tiers of a tiered HG index and moves the contents into an appropriate tier:
```
sp_iqrebuildindex(' table name ’, ' index index name MERGEALL ’)
```
The merge ensures that there is only one active sub-index in a tiered HG index. MERGEALL operations may improve query access time for a tiered index in cases where there are too many deleted records (as shown by sp_iqindexmetadata). MERGEALL will only be supported with an index clause and only if the index specified is an HG index.
RETIER is a keyword specific to HG indexes that changes the format of an HG index from non-tiered HG to tiered HG, or tiered HG to non-tiered HG:
```
sp_iqrebuildindex(‘ table name ’, ' index index name RETIER ’)
```
RETIER toggles the format of an HG index:
- RETIER converts a tiered HG index into a single non-tiered HG index. Tiering metadata is disabled and only one sub-index is maintained.
- RETIER converts a non-tiered HG into a tiered HG index, and pushes the single sub-index which contains all the data into an appropriate tier.
MERGEALL and RETIER will only be supported with an index clause, and only if the index specified is an HG index.

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.

Privileges

You must have EXECUTE privilege on the system procedure. You must also have one of the following:

INSERT ANY TABLE system privilege
INSERT privilege on the table to rebuild an index
You own the table

Remarks

If you specify a column name, sp_iqrebuildindex rebuilds the default FP index for that column; no index name is needed. If you specify the default FP index name assigned by SAP Sybase IQ in addition to the column name, sp_iqrebuildindex returns an error.

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

A column with IQ UNIQUE n value determines whether sp_iqrebuildindex rebuilds the column as Flat FP or NBit. An IQ UNIQUE n value set to 0 rebuilds the index as a Flat FP. An n value greater than 0 but less than 2,147,483,647 rebuilds the index as NBit. NBit columns without an n value are rebuilt as NBit. sp_iqrebuildindex rebuilds an NBit column as NBit, even if you do not specify a count. If you do specify a count, the n value must be greater than the number of unique values already in the index.

If you rebuild a column with a Flat FP index, and the column does not include an IQ UNIQUE n value, sp_iqrebuildindex rebuilds the index as Flat FP up to the limits defined in the FP_NBIT_AUTOSIZE_LIMIT and FP_NBIT_LOOKUP_MB options. Specifying a n value for a flat column throws an error if FP_NBIT_ENFORCE_LIMITS=ON and the cardinality exceeds the count.

The sp_iqrebuildindex default interface allows a user to recreate an entire HG index from an existing FP index. sp_iqrebuildindex re-reads all FP index column values and creates the HG index. This will, however retain all the metadata regarding tier sizes, continuous load size, etc.

Example

Rebuilds the default FP index on column Surname:

sp_iqrebuildindex ‘emp1‘, ‘column dept_id‘

or:

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

Creates a flat FP index on column c1:

CREATE TABLE mytable (c1 int IQ UNIQUE (0))

Converts the default Flat FP index to an Nbit index with an estimated distinct count of 1024:

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.