# 18. Free Text Search

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
Overview
Installation Guide
Quick Start & Tours
Sample ODBC & JDBC Applications
Conceptual Overview
Server Administration
Data Access Interfaces
SQL Reference
SQL Procedure Language Guide
Database Event Hooks
Data Replication, Synchronization and Transformation Services
Web Application Development
XML Support
RDF Database and SPARQL
Web Services
Runtime Hosting
Internet Services
Free Text Search
Basic Concepts
Creating Free Text Indexes
Querying Free Text Indexes
Text Triggers
Generated Tables and Internals
Removing A Text Index
Removing A Text Trigger
Internationalization & Unicode
Performance
Free Text Functions
TPC C Benchmark Kit
Using Virtuoso with Tuxedo
Appendix
Virtuoso Functions Guide

## Abstract

Virtuoso provides a compact and efficient free text indexing capability for text and XML data. A free text index can be created on any character column, including wide and long data.

The contains SQL predicate allows content based retrieval of textual data. This predicate takes a column and a text expression and is true if the pattern of words in the text expression occurs in the column value. There must exist a previously created text index of the column. The text expression can contain single words and phrases connected by boolean connectives or the proximity operator. Words can contain wildcards but must begin with at least three non-wildcard characters if a wildcard is to be used. While it is enough to declare a free text index on a column and then just use the contains predicate for many applications, Virtuoso offers a range of options for tailoring how the indexing works.

If a certain application specific order of search results is desired more frequently than others, it is possible to specify a single or multipart key in the order of which hits will be returned from contains searches. Both ascending and descending order of the key is supported. To restart a search in the middle it is possible to specify a starting and ending key value. This works if the results are generated in the order of the application specific doc ID.

If non-text criteria are often used to filter or sort results of contains searches, it is possible to cluster these non-text data inside the free text index for faster retrieval. It is often substantially faster to retrieve the extra data from inside the text index than to get them from the row referenced by the text index. Such data are called offband data, since they are not actually text but are stored similarly to text.

It is possible to pre-process the text before it is indexed or unindexed. This feature can be used for data normalization and/or for adding content from other than the primary text field being indexed into the index. One example is adding the names of all newsgroups where an article appears to the index when indexing a news article. Thus when retrieving articles based on text and newsgroup, group can be used to very efficiently filter out the hits that are not in the group, even if the text indexed does not itself contain the group name. Another application of the same technique is adding text from multiple columns into the same index.

If the column being indexed is XML data, this can be declared and enforced by the text index. XML data will be indexed specially to support efficient XPATH predicate evaluation with the xcontains predicate.

Text Triggers is a feature that allows the storage of a large body of free text queries and automatically generating hits when documents matching the criteria are added to the index. This is useful for personalized data feeds, user profiles, content classification etc, which Virtuoso can send the results to in an email message. The conditions can be either free text expressions or XPATH expressions for XML content.

The text index can be kept synchronous with the data being indexed, so that the index is updated in the same transaction as the data. The other possibility is to maintain the text index asynchronously as a scheduled task (batch mode), which can execute up to an order of magnitude faster. The asynchronous mode of operation offers substantially higher performance if changes of multiple entries are processed in one batch index refresh.

## Table of Contents

18.1. Basic Concepts
18.2. Creating Free Text Indexes
18.2.1. The CREATE TEXT INDEX statement
18.2.2. Choosing An Application Specific Document ID
18.2.3. The composite Data Type
18.2.4. Free Text Index Examples
18.2.5. Pre-processing and Extending the Content Being Indexed
18.2.6. Hit Scores
18.2.7. Word Ranges
18.2.8. Using Offband Data for Faster Filtering
18.2.9. Order of Hits
18.2.10. Noise Words
18.3. Querying Free Text Indexes
18.3.1. CONTAINS predicate
18.3.2. Comments
18.3.3. Text Expression Syntax
18.4. Text Triggers
18.4.1. Creating Text Triggers
18.4.2. Created Database Objects
18.5. Generated Tables and Internals
18.5.1. Generated Tables and Procedures
18.5.2. The procedures are:
18.5.3. Tables and Procedures Created By Text Triggers
18.6. Removing A Text Index
18.7. Removing A Text Trigger
18.8. Internationalization & Unicode
18.9. Performance
18.9.1. Restrictions
18.10. Free Text Functions

Previous LDAP | Chapter Contents | Next Basic Concepts