Create or recreate an index

The free text indexes are a part of the initial WorkZone installation, but sometimes an index must be recreated, for example if the index has been dropped (removed) but afterwards needs to be recreated.

When you recreate a free text index, you can determine the amount of memory allocated and how many parallel threads are to be allocated to the generation of the index.

Memory

The Memory field defines how much memory is allocated to creating the index. The default value is 10 mb but this should be increased to 100 mb or 200 mb in order to reduce the fragmentation of the index. A fragmented index does not perform as well as an index that is not fragmented. The database server should ideally contain sufficient memory that an increase of the allocated memory will not affect the server negatively.

Parallel threads

The Parallel field defines how many CPU threads are to be allocated to building or rebuilding the index. For large databases, there will be several CPU threads available on the database server and you can allocate multiple threads to building or rebuilding the index. By allocating multiple CPU threads to the building of an index, you can reduce the time required to build the index.

To recreate a free text index

On the WorkZone Content Server, press Start and type ScanSQL to open the ScanSQL form. You can also press ALT+R in windows to open the Run window and type ScanSQL.
In the ScanSQL form, click SjBase to open the SjBase form.
In the SjBase form, click Text Indexes to open the Text Indexes form.
In the Indexes field group, select the index you want to recreate.
In the Text Indexes form, click Settings for... to open the Settings for... form. You can also double-click the index to open the Setting for... form for the index.
In the Settings for ... form, > Create group:
- In the Memory field, specify how much memory to allocate for the creation and recreation of the index.
- Select the $R table - buffer pool = KEEP check box to load the $R table in the Oracle Keep Cache to improve the search speed of the index.
- In the Parallel field, set how many CPU threads to allocate for the creation and recreation of the index.
- Select the Online check box.
Click Save to save your changes and create or recreate the index.