| |
FTS is an indexing subsystem for the Eloquence database introduced with
Eloquence B.08.20. FTS indexes may be used to add full text search capabilities
to applications and support advanced search capabilities that would otherwise
require a full database scan.
Overview
Before the advent of search engines, users had to search manually through
dozens - or hundreds - of documents and tidbits to find what they were looking
for. Nowadays we expect results to come to the user, not the other way around.
The search engine gets the computer to do the work for the user.
While databases for long supported searching by indexes, offering ordered
retrieval and partial key searches, this is of limited use if a word is
not at the beginning of a field and also limits searches to a single field.
A full scan of the database content is prohibitive once data grows
beyond a trivial size.
Eloquence FTS adds search engine capabilities to the database, enabling applications
to support advanced search capabilities with only minor changes.
FTS indexes extend on traditional indexes in a number of ways
- FTS indexes are optimized to qualify result sets rather than individual records.
While the result of an FTS search may qualify a single record, it may as well qualify
a million records without taking much longer. For example, searching all customers
in a specific city should take roughly the same time, regardless if there are only a
few or a few thousand.
- FTS searches may be combined or filtered subsequently. Boolean operators,
such as AND, OR or NOT may be applied to FTS searches.
For example, one may search all customers in a specific state and subsequently
filter out a specific city. Or even search for customers NOT in a specific city.
- Searches on different items may be combined. For example, qualifying database
content by region and recently purchased products.
- Text fields may be parsed to index individual words, allowing to qualify
database entries by keywords or combinations. For example, searching
product descriptions for "torx" would qualify all torx screws (and screw drivers),
regardless where this word occurs in the description. With traditional indexes this
is only efficient if the searched word appears at the beginning of a field.
- Fields may be grouped so searches may span multiple fields. For example,
multiple address fields in a customer record could be used to qualify results.
FTS stucture
The FTS indexing subsystem uses a dictionary to maintain a list of unique words.
For each word a result set identifies any database records using that word.
FTS searches are usually performed within the dictionary and the associated result
set rather than searching individual database records.
Full text search
Eloquence FTS indexes may be used to add full text search capabilities to applications,
to efficiently lookup data for arbitrary words or combinations of
words in indexed fields. While traditional indexes allow searching for partial values
(at the beginning of a field), FTS indexes make it easy to add searching the database for
arbitrary words, with just a few lines of code (or no application changes at all for simple use).
With FTS, text fields are parsed by default and the individual words are indexed.
For example, a supplier "ACME Widget Factory" could be searched for by "acme",
"widget" or "factory". Searching for widgets suppliers other than ACME is just as easy
("widget not acme"). Boolean operators (AND, OR, NOT) may be used to combine or exclude
specific words.
By default FTS indexes are not case sensitive and searching for partial words, wildcards
and ranges is supported. For example, searching for angel@ could qualify "Los Angeles".
FTS fields may be grouped, a word would match regardless of which fields it
originally came from. For example, a customer address with name, street and city fields.
When placed in the same group, words occurring in any of these fields could be used to
qualify the customer.
As FTS searches may be combined with previous FTS results, it is easy to combine information
from multiple fields (even if not grouped), providing complex searches with just a few lines
of code.
For example, when searching for customers in California but not in Redwood one could
search for "State = ca" and subsequently filter the result by "city = and not redwood".
Advanced functionality
In addition to the basic FTS functionality the optional "FTS" license option
enables additional functionality.
-
FTS indexes are no longer limited to a single data set but may refer to a related
master set entry. This allows to maintain aggregated FTS indexes (any words occurring
in either the master or any detail are attributed to the master record), to
qualify both master and detail records or to combine queries on different sets with a
common master set.
-
FTS indexes are not limited to text fields but extend to numeric items or numeric
values in text fields. This allows to efficiently search for numeric values or
ranges of numeric values. For example, to qualify orders from a region above a
specified value.
- Applying search results to other sets. Search results in one data set may be
used to qualify related records in another data set.
- Searching for phonetic representation (soundex). An index field may be designated
to support soundex searches, making it easy to locate entries despite minor differences in
spelling.
- Fuzzy search allows matching mistyped words or phone numbers based on the
similarity of words.
Backwards compatibility
As an option, Eloquence implements "ODX" calls to access the Eloquence FTS functionality.
This allows existing applications relying on ODX/DBI calls to make use of Eloquence FTS
indexes with no or only minor changes.
|
|
