How a full-text search works

To perform a full-text search, you enter a select statement that joins the IDENTITY column from the source table with the id column of the index table, using pseudo columns as needed to define the search. For example, the following query searches for documents in the blurbs table of the pubs2 database in which the word “Greek” appears near the word “Gustibus” (the i_blurbs table is the index table):

select t1.score, t2.copy
from i_blurbs t1, blurbs t2
where t1.id=t2.id and t1.score > 20
and t1.max_docs = 10
and t1.index_any = "<near>(Greek, Gustibus)"

Adaptive Server and the Enhanced Full-Text Search engine split the query processing, as follows:

The Enhanced Full-Text Search engine processes the query:
```
select t1.score, t1.id
from i_blurbs t1
where t1.score > 20
and t1.max_docs = 10
and t1.index_any = "<near>(Greek, Gustibus)"
```
The select statement includes the Verity operator near and the pseudo columns score, max_docs, and index_any. The operator and pseudo columns provide the parameters for the search on the Verity collections—they narrow the result set from the entire copy column to the 10 documents in which the words “Greek” and “Gustibus” appear closest to each other.
Adaptive Server processes the following select statement on the result set that is returned by the Enhanced Full-Text Search engine in step 1:
```
select t1.score, t2.copy
from i_blurbs t1, blurbs t2
where t1.id=t2.id
```
This joins the blurbs and i_blurbs tables (the source table and the index table, respectively) on the IDENTITY column of the blurbs table and the id column of the i_blurbs table.

Figure 2-2 describes how Adaptive Server and the Enhanced Full-Text Search engine process the query.

Figure 2-2: Processing a full-text search query