sp_iqrebuildindex Procedure

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 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.

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.