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.
User must be a table owner, have INSERT permission on a table to rebuild an index on that table, or have the INSERT ANY TABLE system privilege.
|
Parameter |
Description |
|---|---|
|
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 recontructs 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:
|
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 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. Specifiying 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‘or:
call sp_iqrebuildindex (‘empl1‘, ‘column dept_id‘)
CREATE TABLE mytable (c1 int IQ UNIQUE (0))
sp_iqrebuildindex ‘mytable‘, ‘column c1 1024‘or:
call sp_iqrebuildindex (‘mytable‘, ‘column c1 1024‘)