Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Full-Text Search

Specialized Data

A support desk keeps its tickets' text and needs the tickets that mention a problem, whatever their wording's case or accents. This guide searches them with TEXT_MATCHES (extension), indexes them so a search reads only the tickets it finds, and then searches them by pattern and ranks them with CONTAINS and RANK of SQL/MM Full-Text. The reference gives every rule.

The tickets

CREATE SCHEMA AUTHORIZATION ANN;
CREATE TABLE TICKETS (ID INTEGER PRIMARY KEY, SUBJECT VARCHAR(80), BODY VARCHAR(2000));
INSERT INTO TICKETS VALUES
    (1, 'Login fails', 'I can''t log in since the update. The password reset email never arrives.'),
    (2, 'Invoice wrong', 'The March invoice charges twice for the same order.'),
    (3, 'Résumé upload', 'Uploading a résumé as PDF fails with an error.'),
    (4, 'Password rules', 'Which characters may a password hold? The reset page does not say.'),
    (5, 'Refund', 'Please refund the second charge on my invoice.');
COMMIT;
OK
OK
OK
OK

Searching

TEXT_MATCHES is true of a text that holds every word of the search, in any order and anywhere in it:

SELECT ID, SUBJECT FROM TICKETS WHERE TEXT_MATCHES(BODY, 'password reset') ORDER BY ID;
           ID | SUBJECT
--------------+--------------------------------------------------------------
            1 | Login fails
            4 | Password rules
(2 rows)

Words are compared normalized, case folded and accents taken off, so RESUME finds résumé:

SELECT ID FROM TICKETS WHERE TEXT_MATCHES(BODY, 'RESUME pdf');
           ID
--------------
            3
(1 row)

A word is matched as written, not by its stem, so invoices finds no ticket that says invoice:

SELECT ID FROM TICKETS WHERE TEXT_MATCHES(BODY, 'invoice') ORDER BY ID;
SELECT ID FROM TICKETS WHERE TEXT_MATCHES(BODY, 'invoices');
           ID
--------------
            2
            5
(2 rows)
           ID
--------------
(0 rows)

Stop words, as the and can't, are left out of a search. A search of stop words alone is true of every text:

SELECT ID, TEXT_MATCHES(BODY, 'can''t') AS MATCHED FROM TICKETS WHERE ID < 3 ORDER BY ID;
           ID | MATCHED
--------------+---------
            1 | TRUE
            2 | TRUE
(2 rows)

Indexing

Without an index, a search reads every ticket. An index of the method fulltext (extension) keeps each word's tickets, so a search reads those alone. It is kept up to date as tickets change, and the planner uses it when it costs less than reading the table, which these few tickets may not. An index is made outside a transaction, so the searches' is ended first:

COMMIT;
CREATE INDEX TICKETS_BODY ON TICKETS (BODY) USING FULLTEXT;
SELECT OPTION_NAME, OPTION_VALUE, VALUE_SOURCE FROM INFORMATION_SCHEMA.INDEX_OPTIONS
    WHERE INDEX_NAME = 'TICKETS_BODY' ORDER BY OPTION_NAME;
COMMIT;
OK
OK
 OPTION_NAME                                                  | OPTION_VALUE                                                 | VALUE_SOURCE
--------------------------------------------------------------+--------------------------------------------------------------+--------------------------------------------------------------
 FULLTEXT.B                                                   | 7.5E-1                                                       | DEFAULT
 FULLTEXT.K1                                                  | 1.2E0                                                        | DEFAULT
 FULLTEXT.LANGUAGE                                            | ENGLISH                                                      | DEFAULT
 FULLTEXT.RANKING                                             | BM25                                                         | DEFAULT
 FULLTEXT.STOP_WORDS                                          | DEFAULT                                                      | DEFAULT
(5 rows)
OK

The index finds the rows a search without it finds. Its options choose how it reads text. A desk whose tickets often say please can leave it out of the index with its own stop words. That builds the index again, while the table stays open to others. A language the tokenizer does not know is refused:

ALTER INDEX TICKETS_BODY SET (FULLTEXT.STOP_WORDS = 'please, the');
ALTER INDEX TICKETS_BODY SET (FULLTEXT.LANGUAGE = KLINGON);
OK
turborel: SQLCODE -103 (SQLSTATE 2A000): FULLTEXT.LANGUAGE is one of ENGLISH, NONE, not KLINGON

An index is of one character string or FULLTEXT column:

CREATE INDEX TICKETS_SUBJECT ON TICKETS (SUBJECT, BODY) USING FULLTEXT;
CREATE INDEX TICKETS_ID ON TICKETS (ID) USING FULLTEXT;
turborel: SQLCODE -900 (SQLSTATE 0A000): the fulltext index method indexes one character string or FULLTEXT column
turborel: SQLCODE -900 (SQLSTATE 0A000): the fulltext index method indexes one character string or FULLTEXT column

Searching by pattern

TEXT_MATCHES asks for every word. CONTAINS reads a search pattern of ISO/IEC 13249-2, in which words are written in double quotes and may be stemmed, joined by &, | and NOT, or asked to stand near each other. It gives 1 or 0. A stemmed word finds invoice and invoices alike, and NEAR finds password a few words from reset, in either order:

SELECT ID, SUBJECT FROM TICKETS WHERE CONTAINS(BODY, 'STEMMED FORM OF "invoices"') = 1 ORDER BY ID;
SELECT ID FROM TICKETS WHERE CONTAINS(BODY, '"password reset" | "refund"') = 1 ORDER BY ID;
SELECT ID FROM TICKETS WHERE CONTAINS(BODY, '"password" NEAR "reset" WITHIN 3 WORDS ANY ORDER') = 1 ORDER BY ID;
           ID | SUBJECT
--------------+--------------------------------------------------------------
            2 | Invoice wrong
            5 | Refund
(2 rows)
           ID
--------------
            1
            5
(2 rows)
           ID
--------------
            1
            4
(2 rows)

RANK scores how well a ticket matches, by BM25 over the index's statistics, so the desk can show the best few first. With the index, such a query reads the tickets in rank order and stops after the first rows:

SELECT ID, SUBJECT, RANK(BODY, '"invoice" | "charge%"') AS SCORE FROM TICKETS
    WHERE CONTAINS(BODY, '"invoice" | "charge%"') = 1
    ORDER BY RANK(BODY, '"invoice" | "charge%"') DESC FETCH FIRST 2 ROWS ONLY;
           ID | SUBJECT                                                      |                    SCORE
--------------+--------------------------------------------------------------+--------------------------
            5 | Refund                                                       |     2.6002217094092983E0
            2 | Invoice wrong                                                |      2.468305162708714E0
(2 rows)

The pattern reference has every form of a pattern, phrases and wild cards, sentences and paragraphs, sound-alike and misspelt words, and thesauri. The reference has the FULLTEXT type, which keeps a document's language with its text, and how ranking can be tuned.