Rebuilds column indexes.
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.
sp_iqrebuildindex (<table_name>, <index_clause>)
sp_iqrebuildindex <tb1>, <'column tb1.c1'>
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.
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 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.
sp_iqrebuildindex(‘<table name>’, ' index <index name> [ MERGEALL | RETIER ] ’)
sp_iqrebuildindex(' <table name> ’, ' index <index name> MERGEALL ’)
sp_iqrebuildindex(‘ <table name> ’, ' index <index name> RETIER ’)
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.
You must specify the keywords column and index. These keywords are not case-sensitive.
This procedure does not support TEXT indexes. To rebuild a TEXT index you must drop and re-create the index.
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 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.
sp_iqrebuildindex ‘emp1‘, ‘column dept_id‘
call sp_iqrebuildindex (‘empl1‘, ‘column dept_id‘)
CREATE TABLE mytable (c1 int IQ UNIQUE (0))
sp_iqrebuildindex ‘mytable‘, ‘column c1 1024‘
call sp_iqrebuildindex (‘mytable‘, ‘column c1 1024‘)