KI_REBUILD

Rebuild a tables indexes from the data rows

Synopsis:
CALL KI_REBUILD handle, method, [nIndex] TO status
status = 'ki_rebuild( handle, method, [nIndex] )
ArgumentEnumerationPurpose
handleHandle
methodKDB_REBUILD_ENUMMethod
nIndexIndex number
statusKDB_ERROR_ENUMReturn status
>KI_REBUILD

KI_REBUILD

This function can rebuild all the indices for a table in one operation as this is much faster than dropping and recreating the indexes separately.

The nIndex argument is optional and if no supplied then zero is assumed. It is only relevant for _KDB_REBUILD_INDEX and _KDB_REBUILD_WS_INDEX where it specifies the index to be rebuilt (counted from 1). Using these methods with an nIndex of zero will rebuild all the relevant indexes.

Method Purpose
_KDB_REBUILD_FAST Fast rebuild of index set by scanning sequence set only
_KDB_REBUILD_FULL Slow but sure rebuild by scanning data rows
_KDB_REBUILD_CONSOLIDATE Consolidate extents in a KDB6 table. In a KDB7 table it will fixup any broken data chains and recover lost BLOBs.
_KDB_REBUILD_INDEX Rebuild one B-tree index as specified by the nIndex argument. If zero then all the B-tree indexes will be rebuilt.
_KDB_REBUILD_WS_INDEX Rebuild one WS index as specified by the nIndex argument. If zero then all the WS indexes will be rebuilt.

KI_REBUILD can rebuild directly from the data rows by setting the method parameter to _KDB_REBUILD_FULL. This is only required if indexes are known to be invalidated or damaged. It requires a sort and so must have workspace of up to twice the total index size. The environment variable WORKSPACE can be used to specify where the temporary storage is located.

It may be more appropriate to do a fast rebuild if you are rebuilding the index for performance reasons. This just scans the bottom level of each KDB index, called the sequence set, repopulating the higher level index blocks. This will pack all the blocks in the index set to 100%. A fast rebuild is selected by setting the method parameter to _KDB_REBUILD_FAST. If the fast rebuild finds that an index is damaged then it will switch to a slow rebuild automatically.

If a full rebuild on a KDB table finds duplicate keys all indices are rebuilt but an error status of KE_DUPLICATE is returned. To remove the duplicates you should copy the table.

If a KDB type 6 table has reached the maximum number of extents, or if you wish to consolidatate what extents there are, then run KI_REBUILD with a method value of _KDB_REBUILD_CONSOLIDATE. This does not rebuild the indexes so you may want to do that subsequently.

Using a method of _KDB_REBUILD_CONSOLIDATE on a KDB type 7 table will relink the data chains that connect the data pages and rebuild the free page chain. If there are any disconnected BLOBs then they will be relinked or dropped if their parent row has been deleted. The indexes are not rebuilt.

SQL considerations

On Oracle the fast rebuild will do an ALTER INDEX COALESCE to compact index blocks. The _KDB_REBUILD_FULL and the _KDB_REBUILD_INDEX modes will do an ALTER INDEX REBUILD to completely rebuild the index in place. KCML adds the PARALLEL and NOLOGGING clauses for performance reasons and therefore rebuild operations are not replayable on recovery.

On PostgreSQL both options issue a REINDEX INDEX statement to rebuild the index in place.

_KDB_REBUILD_CONSOLIDATE is silently ignored on SQL databases.

History
Introduced in KCML 3.00
See Also:
KI_CLOSE, KI_CLOSE_TABLE, KI_COPY_TABLE, KI_CREATE, KI_CREATE_TMP, KI_DB_NAME, KI_DELETE_ROWS, KI_DROP_TABLE, KI_EXTEND, KI_GET_HANDLE, KI_GET_SERIAL, KI_GROW, KI_INFO, KI_INITIALISE, KI_LOCK_ALL, KI_LOCK_OWNER, KI_MOVE_ROWS, KI_MOVE_TABLE, KI_OPEN, KI_PREFETCH_ROWS, KI_READ, KI_READ_HOLD, KI_READ_HOLD_NEXT, KI_READ_HOLD_PTR, KI_READ_NEXT, KI_READ_PTR, KI_READ_RAW, KI_RESET_CACHEID, KI_REWRITE, KI_SET_DATESTAMP, KI_SET_EXTENT, KI_SET_ROWS, KI_SET_SERIAL, KI_SET_TIMESTAMP, KI_SET_USERSTAMP, KI_SET_WLOCK_COUNT, KI_SIZE_FILE, KI_START, KI_START_BEG, KI_UNLOCK, KI_UNLOCK_FILE, KI_VERIFY, KI_WMODE_BITMAP, KI_WRITE, KI_WRITE_PTR, KI_WRITE_RAW, KI_WS_CREATE, KI_WS_DELETE, KI_WS_OPEN, KI_WS_READ, KI_WS_REWRITE, KI_WS_WRITE
KCML database status codes