Using a stored procedure search

The stored procedure search works differently from the SQL scalar search functions. At text index creation, you have to specify which columns out of the table or view are returned to the end user. This data is stored in a cache in main memory. This enables the stored procedure search to return search results extremely quickly. The cache needs to be activated before it can be used and there is a corresponding deactivate command.

Figure 4. Using a stored procedure search
Using a stored procedure search

The ACTIVATE command loads data into either a temporary cache (which is created from scratch on activation), or a persistent cache, which is maintained on disk.

Using the stored procedure for searching requires memory calculations, such as how much memory is required and how much free memory should be left for index updates.

The stored procedure can work on text indexes that are created on views. However, as triggers can not be created on views, any changes are not automatically recognized. You can manually add the changed information to the log table, or you can work with the RECREATE option.

Use the stored procedure search for high performance/high scalability applications that are interested in text-search-only queries. For example, queries that do not need to join text search results with the results of other complex SQL conditions.

The main functional differences to the SQL scalar search functions are:

Note that for this option, a large amount of main memory must be available. For additional information, see Appendix B. Using large amounts of memory.