Basic text search performance

The performance of basic text search queries depends on the bts index, disk space, configuration parameters, and BTS virtual processors.

When you create a bts index, include the following index parameters to improve the performance of basic test search queries:

Set the delete index parameter to remove index information for deleted documents and release disk space. You can optimize the index manually or automatically after every delete operation.
Set the query_default_field=* index parameter to create a composite index. When you run a basic text search query, the text from all the columns is searched in the contents field as if it was one string. This method can result in better query performance than using a UNION clause to combine the results of multiple queries on multiple bts indexes.

Include the following index parameters to improve the performance of building the bts index:

Set the tempspace index parameter to the name of a temporary sbspace. Building the index in a temporary sbspace is faster than building the index in an sbspace that logs transactions.
Set the xact_ramdirectory=yes index parameter to build the bts index in memory. Building the index in memory can be faster than building the index in a temporary sbspace.

The bts index is in an sbspace. If you defined multiple BTS virtual processors, each one can simultaneously process a different transaction. However, when a transaction that contains INSERT, DELETE, or UPDATE statements that affect the bts index is being committed, the transaction acquires an exclusive lock on the bts index. Any other concurrent transaction waits for up to 15 minutes for the lock to be released.

The bts index works in READ COMMITTED isolation level regardless of the isolation level that is set in the database server. The READ COMMITTED isolation level provides access only to rows that are committed. Uncommitted rows from other concurrent transactions are not accessible.