KI_WS_ADD_INDEX
| Argument | Enumeration | Purpose |
|---|---|---|
| handle | Handle | |
| nPath | Word search path for index | |
| sIdxInfo$ | Index descriptor of type KDB_INFO_WSX | |
| sCols$ | Columns for the temporary index (comma separated) | |
| sOrderByCols$ | Any order by columns | |
| noisefile$ | Optional noise filename | |
| status | KDB_ERROR_ENUM | Return status |
KI_WS_ADD_INDEX
Word search indices have a significant performance overhead on volatile tables with lots of inserts, updates and deletes. For each such operation a number of inserts and deletes have to be made to the word search index depending on the number of words affected in the row. Sometimes an application may want to search the table on an infrequent ad hoc basis for which the overhead of maintaining the word index is excessive. To handle this case a second type of temporary word index is available in KCML.
The KI_WS_ADD_INDEX function allows you to specify a temporary word index which allows KI_WS_START and KI_WS_READ_NEXT to be used on tables that do not have permanent word indices in place. There is no actual index here and, when searching using this sort of index, KCML will table scan the table matching each row in turn. Searches are therefore much more expensive but there is no ongoing overhead on inserts and updates. This technique may not be suitable for very big tables because of the time such a search may take to complete. Nor is it suitable for searches that are performed frequently.
After the index is defined using KI_WS_ADD_INDEX multiple searches can be performed using the handle but information about the temporary index is lost when the table handle is closed.
The maximum number of rows in the result set is limited to the value of the handle attribute _KDB_HAND_ATTR_WSMAXROWS. After that number of rows the result set is truncated thus avoiding a significant delay in the KI_WS_START operation.
The path nPath number should be between 1 and 4 and not already used by another index.
The sIdxInfo$ record should be an instance of a KDB_INFO_WSX record. It should be initialized to ALL(00) and any of the following fields can be filled in to define the index:
| Field | Purpose |
|---|---|
| INFO_WSX_MinWordLen | Minimum word length, defaults to 3 if left as zero |
| INFO_WSX_MaxWordLen | Maximum word length, defaults to 8 if left as zero |
| INFO_WSX_NonAlphaChars$ | Non-alpha characters allowed |
The following optional fields can also be filled in and will then overide the explicit column names sCols$ and sOrderByCols$ which may be omitted or left blank.
| Field | Purpose |
|---|---|
| INFO_WSX_nCols | Number of columns in the WS index key. |
| INFO_WSX_kcol(_KDB_MAX_WS_KEY_COLS) | Index column ordinals |
| INFO_WSX_nOrderByCols | Number of columns in the order by sort key. |
| INFO_WSX_OrderByCols(_KDB_MAX_KEY_COLS) | Order by column ordinals |
All other fields in the record should be left as zero.
The optional sCols$ argument is a comma separated list of the column names defining the word index. The names may be prefixed with the table column prefix or it may be omitted.
The optional sOrderByCols$ argument is a comma separated list of the column names defining the ordering of the result set. The names may be prefixed with the table column prefix or it may be omitted. If this argument is omitted or blank then the result set will not be sorted and will appear in table scan order.
The optional noisefile$ argument specifies an XML file containing exclusions, inclusions and replacements to be applied when parsing words. See CREATE WORD INDEX for more details about noise files.
DIM wx$_KDB_INFO_WSX wx$ = ALL(HEX(00)) FLD(wx$.INFO_WSX_MaxWordLen) = 6 FLD(wx$.INFO_WSX_MinWordLen) = 2 CALL KI_WS_ADD_INDEX h, 2, wx$, "CODE,DESC,DEPTTYPE", "DEPTTYPE" TO s
This function is only available for type 7 KDB and SQL database tables.