# 18. Free Text Search

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
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

## 18.10. Free Text Functions

batch object vt_batch ();

Returns a vt batch object.

vt_batch_d_id (in d_id any);

Specify a document to update in a vt batch.

vt_batch_feed (in vt_batch any, in text_or_xml any, in is_del integer, [in is_xml integer]);

Add words to a free text update batch.

vt_batch_feed_offband (in vt_batch any, in clustered_values string, in is_del integer);

Add offband information to a free text update batch.

integer DB.DBA. VT_BATCH_UPDATE (in table varchar, in flag varchar, in interval_minutes integer);

Set batch mode update of free text indexing.

vt_is_noise (in word varchar, in encoding varchar, in language varchar);

determines whether input is a noise word

Text index log table. In case of batch update mode this table is used to store a log of actions over text indexed table.

    VTLOG_<table> (
      VTLOG_<document_id_col> ANY NOT NULL PRIMARY KEY,
    			-- references text indexed table by document id
      SNAPTIME DATETIME,
    			-- time of insert/update/delete action
      DMLTYPE VARCHAR (1),
    			-- type of log 'I' 'U' 'D' for insert, update or delete
      VT_DISTINCT_WORDS LONG VARBINARY,
    			-- in case of update or delete are stored words which should be deleted
      VT_OFFBAND_DATA LONG VARCHAR
    			-- in case of update or delete are stored offband data should be deleted
    )

Text index batch procedure. This procedure log, process and stores already filled _vt_batch, caused storing of words in index table.

    VT_BATCH_PROCESS_<table> (inout _vt_batch any)

Text trigger hook function. This function, for existing text trigger definition, applies the filtering queries (if defined) to the _strings, and if the _vt_batch matches any of them then add new record in HITS table (see Text trigger)

    VT_HITS_<table> (inout _vt_batch any, inout _strings any);

Text index words table. This table maintains distinct words collected from all documents from text indexed table.

    <table>_<column>_WORDS (
      VT_WORD VARCHAR,  -- distinct word
      VT_D_ID ANY,  -- referencing the first matching
    			-- document id in text indexed table
      VT_D_ID_2 ANY,  -- referencing the last matching document
    			-- id in text indexed table
      VT_DATA VARCHAR,  -- string with document id's and word
    			-- positions where the word is matched
      VT_LONG_DATA LONG VARCHAR, -- the same as VT_DATA but in a
    			-- case of large amount of data
      PRIMARY KEY (VT_WORD, VT_D_ID)
    )

Text indexing procedure, using for clearing and creating the text index. If flag is equal to 1 then index data will be cleared, else if equal to 0 then index data will generated. Note that for large tables generating the index can run for a long time and the server will go into atomic mode.

    VT_INDEX_<table> (in _flag integer)

Procedure for incremental update of text index. In case of batch mode update this procedure must be called to process the entries in log table and refresh text index data. Before calling this function the contains/xcontains predicate may not match the newest inserted, updated or deleted documents. This function is also registered for scheduled action if the vt_batch_update() function is called with refresh interval greater than zero. (See vt_batch_update function)

    VT_INC_INDEX_<table> ()

Text indexing and unindexing hook procedures. These are user-defined procedure which can access additional related data and perform preprocessing and call vt_batch_feed inside. These procedures receive the free text id as argument and can use this to retrieve data related to the row being indexed. If the text index is created with 'USING FUNCTION' clause then internally generated procedures and functions will include calls to these. If these procedures return 1 then the caller will skip filling a vt_batch, assuming the hook function already filled it, otherwise the caller will proceed as if there had been no hook.

The difference between these two functions is that ..._INDEX_HOOK will be called upon insertion of new data or after update, but .._UNINDEX_HOOK will be called after delete or before update on the text indexed table.

Note:

This function is USER-DEFINED, the user can create it before or after creating a text index.

In the second case the text index MUST be create with the 'NOT INSERT' option. After the hook is defined, the index can be filled with with 'VT_INDEX_<table>(0)' procedure.

    <table>_<column>_INDEX_HOOK (inout _vt_batch any, inout d_id any)
    <table>_<column>_UNINDEX_HOOK (inout _vt_batch any, inout d_id any)

Free Text

    create table fth (id integer not null primary key, dt varchar, c1 varchar);

    create procedure fth_dt_index_hook (inout vtb any, inout d_id integer)
    {
      declare data any;
      data := coalesce ((select concat (dt, ' ', c1)
    	from fth where id = d_id), null);
      if (data is null)
        return 0;
      vt_batch_feed (vtb, data, 0);
      return 1;
    }

    create procedure fth_dt_unindex_hook (inout vtb any, inout d_id integer)
    {
      declare data any;
      data := coalesce ((select concat (dt, ' ', c1)
    	from fth where id = d_id), null);
      if (data is null)
        return 0;
      vt_batch_feed (vtb, data, 1);
        return 1;
    }

    insert into fth values (1, 'abc', 'one');

    create text index on fth (dt) with key id using function;

test the text index:

    select id from fth where contains (dt, 'abc');
    select id from fth where contains (dt, 'one');

Both select statements will return 1 because the content is concatenated with an additional column.

Note that in the case of using additional columns, they should be added as offband data to the text index, otherwise update them will not affect the index.

    <table>_<column>_QUERY table (see Text trigger definition)
    <table>_<column>_USER table (see Text trigger definition)
    <table>_<column>_HIT table (see Text trigger definition)
    TT_QUERY_<table>_<column> procedure (see Text trigger definition)

Table for text indexes system information

    SYS_VT_INDEX (
      VI_TABLE VARCHAR,  -- Fully qualified text indexed table name
      VI_INDEX VARCHAR,  -- Index name
      VI_COL VARCHAR,  -- Data column name
      VI_ID_COL VARCHAR,  -- Document id column name
      VI_INDEX_TABLE VARCHAR,  -- fully qualified name of table with words
    			-- (See: <table>_<column>_WORDS table)
      VI_ID_IS_PK INTEGER,  -- if the document id is specified by user or
    			-- used primary key then it equal to 1
      VI_ID_CONSTR VARCHAR,  -- serialized value with id column(s) names
      VI_OFFBAND_COLS VARCHAR,  -- serialized value of offband data columns names
      VI_OPTIONS VARCHAR,  -- reserved
      VI_LANGUAGE VARCHAR,  -- language which applied to the document contents
      PRIMARY KEY (VI_TABLE, VI_COL))

Previous Performance | Chapter Contents | Next Contents of TPC C Benchmark Kit