Full Text Index (FTI)

The Full Text Index (FTI) definition is coupled with the use of 'Contains' search operator.

Before you can use this operator, a Full Text Index (FTI) must be defined for the selected string attribute. The creation of the index is necessary for the use of the 'Contains' operator which leads to faster searches in texts containing certain words. For example, typically it is used for columns with longer text such as Description, Shorttext etc.

Defining the Full Text Index

To define the full text index, do the following:

Select the desired string attribute in the 'Used Attributes' pane.
Click the 'FTI' button.
The 'Create new full text index' dialog box appears.

Because the full text index is not updated automatically on change, the following synchronization options are available:

ORACLE
- Synchronize index manually
  The index must be synchronized manually. Typically a database administrator schedules a database job that will synchronize the index at some intervals by calling the CTX_DDL.SYNC_INDEX('<index name>', '10M') stored procedure. This option is most suitable for data that never changes or changes at known intervals (e.g. data is loaded once a day at night).
- Synchronize index on commit
  The index will be synchronized immediately upon committing a database transaction after a series of DML statements (INSERT/UPDATE/DELETE).
  
  Warning: Set this option only when the column is updated or new records inserted (a very seldom occurrence) otherwise you risk serious performance problems!
- Synchronize index every x minute
  The index will be synchronized every x minutes. To avoid performance problems and fragmented index, do not set the value too low (e.g. less than 5 min). The correct value depends on how often the data is modified, inserted and other similar factors.
MSSQL
- Manually
  Specifies that the tracked changes must be propagated manually by calling the ALTER FULLTEXT INDEX … START UPDATE POPULATION Transact-SQL statement (manual population). You can use the SQL Server Agent to call this Transact-SQL statement periodically.
- Automatically
  Specifies that the tracked changes will be propagated automatically as the data is modified in the base table (automatic population). Although the changes are propagated automatically, they may not be reflected immediately in the full-text index. AUTO is the default value.
- Off
  Specifies that the SQL Server does not keep a list of changes to the indexed data. When NO POPULATION is not specified, the SQL Server populates the index fully after it is created.
  
  The NO POPULATION option can be used only when CHANGE_TRACKING is OFF. When NO POPULATION is specified, SQL Server does not populate an index after it is created. The index is only populated after the user executes the ALTER FULLTEXT INDEX command with the START FULL POPULATION or START INCREMENTAL POPULATION clause.
DB2
- Manually
  The index must be manually synchronized by executing UPDATE INDEX <schema>.<index name> FOR TEXT.
  
  This is the most suitable option for data that never change or the changes at known intervals (e.g. the data is loaded once a day at night).
- Every (x minutes)
  Index will be synchronized at a specific time but only after at least 'n' text changes. To avoid performance problems and fragmented index, do not set the values too low. The optimal values depend on how often data is modified and inserted and other factors.
  
  UPDATE FREQUENCY specifies how often the update services should check if an update is required (minimum is 5 minutes).
  
  UPDATE MINIMUM specifies the minimum number of changes that must be made to the underlying table before an update can be triggered.
After you select the corresponding option, click the 'Execute' button.
In order to be able to successfully create the full-text index, the database user used for database connection must have the corresponding system privileges. For example, unless you are using the SYSTEM user (with admin privileges), you must have the CREATE INDEX system privilege on Oracle.

Note:If the button is not enabled, it is because the possibility to execute SQL statements is not enabled by a special parameter (-Dde.usu.s3.gui.console.sql.enabled=true) in the admin.bat file. Alternatively, you can copy the generated SQL statement to a DB tool of your preference and execute it in it.
After the Index has been created, a status info about the process is available on the 'Detected Changes' tab below.
The detected changes must be applied (saved).

If the Index has been created successfully, the 'contains' operator can be used now with the selected string attribute.

Changing Index Synchronization

Index synchronization specified in the 'Create new full-text index' dialog during index creation may prove inappropriate in real-life use cases. For example, some users may experience the following problem:

The user working with a catalog creates a copy of an object named 'xyz' and names it 'xyz - copy'.
Shortly afterwards the user performs a search using the 'Contains' operator.
The search result is expected to list both object 'xyz' and object 'xyz - copy'. The new object 'xyz - copy' is, however, not found by the search.

An explanation of this unexpected behaviour probably lies in index synchronization. The full-text index is synchronized (updated) in intervals determined by the 'Synchronize index/Every (x minutes)' value found in the 'Create new full-text index' dialog. As a result, it takes some time before the index reflects the new entry.

The default value for synchronization option 'Every (x minutes)' is 60 minutes. If this interval is not acceptable in the given environment, it can be decreased. Note, however, that for performance reasons it should not be set too low, the recommended minimum being 5 minutes.

Changing the synchronization interval

The 'Create new full-text index' dialog cannot be used for editing of already specified full-text indexing. If a change is needed, do the following:

Use the Valuemation Console or another database tool to drop the index.
Recreate the index with changed values set in the 'Create new full-text index' dialog.

Case Sensitivity and the Default Language

The default case sensitivity of the index and the default language are detected automatically on Oracle installations. When the 'Create new full text index' dialog box opens, the selected radio button indicates the default case sensitivity of the index. The detected language of the default language of the DB server is shown on the right.

Note: The case sensitivity and the default language controls work only for Oracle in this dialog box.

The usual detection scenarios are:

For German language: The default case sensitivity = case sensitive.

For English language (and other languages): The default case sensitivity = case insensitive.

You can change the index case sensitivity using the appropriate radio button.

When an index is created as case sensitive, you will have to query words exactly as they appear in text.

Note that the the case sensitivity must be set prior to the index creation. Once the index is created, its case sensitivity cannot be changed (the index would have to be recreated).

The default language detected is the default language of the DB server default installation. The language determines various preferences such as case sensitivity, alternate spelling and stop words (the words which will not be indexed, for example "a, the, is, are, you..." in English).
The default language can be only changed by a DB administrator. If you want to index text in a different language, you should create a preference, stop list and other full-text objects and specify them manually in the CREATE INDEX...PARAMETERS statement.

Please see the Oracle Text documentation for details.

Note: When you change the case sensitivity settings, a warning dialog box appears stating that the procedure may fail due to some other Oracle restrictions. This is not an error of the Valuemation application.

Adding the Full Text Search Component to an Existing Installation

For the Full Text Search to be available at all, the Full Text Search component must be installed at the database level. If the component is missing, the "Full Text is not installed. Please contact the database administrator." error message gets displayed.

The problem can be fixed by adding the component to the existing installation. This usually involves running the install tool and selecting the 'Add features...' option. The process, however, will differ depending on the database platform and version.

Please refer to the installation documentation of your respective platform.

Helpful links: