<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="18. Free Text Search" />
<meta name="dc.subject" content="18. Free Text Search" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="freetext.html" title="Chapter Contents" />
<link rel="prev" href="ftperformance.html" title="Performance" />
<link rel="next" href="contents.html" title="Contents" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>18. Free Text Search</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="fttfuncs" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>18. Free Text Search</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="freetext.html">Chapter Contents</a> | <a class="link" href="ftperformance.html" title="Performance">Prev</a> | <a class="link" href="tpcc.html" title="TPC C Benchmark Kit">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="freetext.html">Free Text Search</a>
</div>
<br />
<div>
<a href="txtidxquickstart.html">Basic Concepts</a>
</div>
<div>
<a href="creatingtxtidxs.html">Creating Free Text Indexes</a>
</div>
<div>
<a href="queryingftcols.html">Querying Free Text Indexes</a>
</div>
<div>
<a href="txttrig.html">Text Triggers</a>
</div>
<div>
<a href="tablesandinternals.html">Generated Tables and Internals</a>
</div>
<div>
<a href="droptxtindex.html">Removing A Text Index</a>
</div>
<div>
<a href="droptxttrig.html">Removing A Text Trigger</a>
</div>
<div>
<a href="ftinternationalization.html">Internationalization & Unicode</a>
</div>
<div>
<a href="ftperformance.html">Performance</a>
</div>
<div class="selected">
<a href="fttfuncs.html">Free Text Functions</a>
</div>
<br />
</div>
<div id="text">
<a name="fttfuncs" />
<h2>18.10. Free Text Functions</h2>
<div class="funcsynopsis">
<div class="funcsynopsis">
<a name="fproto_batch" />
<span class="funcdef">batch object <a href="fn_vt_batch.html">
<span class="function">vt_batch</span>
</a>
</span>
(<span class="paramdef" />);
</div>
</div>
<p>Returns a vt batch object.</p>
<div class="funcsynopsis">
<div class="funcsynopsis">
<a name="fproto_batch_d_id" />
<span class="funcdef">
<a href="fn_vt_batch_d_id.html">
<span class="function">vt_batch_d_id</span>
</a>
</span>
(<span class="paramdef">in <span class="parameter">d_id</span> any</span>);
</div>
</div>
<p>Specify a document to update in a vt batch.</p>
<div class="funcsynopsis">
<div class="funcsynopsis">
<a name="fproto_batch_feed" />
<span class="funcdef">
<a href="fn_vt_batch_feed.html">
<span class="function">vt_batch_feed</span>
</a>
</span>
(<span class="paramdef">in <span class="parameter">vt_batch</span> any</span>,
<span class="paramdef">in <span class="parameter">text_or_xml</span> any</span>,
<span class="paramdef">in <span class="parameter">is_del</span> integer</span>,
<span class="paramdefoptional">[in <span class="optional">is_xml</span> integer]</span>);
</div>
</div>
<p>Add words to a free text update batch.</p>
<div class="funcsynopsis">
<div class="funcsynopsis">
<a name="fproto_batch_feed_offband" />
<span class="funcdef">
<a href="fn_vt_batch_feed_offband.html">
<span class="function">vt_batch_feed_offband</span>
</a>
</span>
(<span class="paramdef">in <span class="parameter">vt_batch</span> any</span>,
<span class="paramdef">in <span class="parameter">clustered_values</span> string</span>,
<span class="paramdef">in <span class="parameter">is_del</span> integer</span>);
</div>
</div>
<p>Add offband information to a free text update batch.</p>
<div class="funcsynopsis">
<div class="funcsynopsis">
<a name="fproto_vt_batch_update" />
<span class="funcdef">integer DB.DBA.<a href="fn_vt_batch_update.html">
<span class="function">VT_BATCH_UPDATE</span>
</a>
</span>
(<span class="paramdef">in <span class="parameter">table</span> varchar</span>,
<span class="paramdef">in <span class="parameter">flag</span> varchar</span>,
<span class="paramdef">in <span class="parameter">interval_minutes</span> integer</span>);
</div>
</div>
<p>Set batch mode update of free text indexing.</p>
<div class="funcsynopsis">
<div class="funcsynopsis">
<a name="fproto_vt_is_noise" />
<span class="funcdef">
<a href="fn_vt_is_noise.html">
<span class="function">vt_is_noise</span>
</a>
</span>
(<span class="paramdef">in <span class="parameter">word</span> varchar</span>,
<span class="paramdef">in <span class="parameter">encoding</span> varchar</span>,
<span class="paramdef">in <span class="parameter">language</span> varchar</span>);
</div>
</div>
<p>determines whether input is a noise word</p>
<p>
Text index log table. In case of batch update mode this table is used to
store a log of actions over text indexed table.
</p>
<div>
<pre class="programlisting">
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
)
</pre>
</div>
<p>
Text index batch procedure. This procedure log, process and stores already filled _vt_batch,
caused storing of words in index table.
</p>
<div>
<pre class="programlisting">
VT_BATCH_PROCESS_<table> (inout _vt_batch any)
</pre>
</div>
<p>
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)
</p>
<div>
<pre class="programlisting">
VT_HITS_<table> (inout _vt_batch any, inout _strings any);
</pre>
</div>
<p>
Text index words table. This table maintains distinct words collected from all documents from text indexed table.
</p>
<div>
<pre class="programlisting">
<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)
)
</pre>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
VT_INDEX_<table> (in _flag integer)
</pre>
</div>
<p>
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)
</p>
<div>
<pre class="programlisting">
VT_INC_INDEX_<table> ()
</pre>
</div>
<p>
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.
</p>
<p>
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.
</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>This function is USER-DEFINED, the user can create it before or after creating a text index.
</p>
</div>
<p>
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.
</p>
<div>
<pre class="programlisting">
<table>_<column>_INDEX_HOOK (inout _vt_batch any, inout d_id any)
<table>_<column>_UNINDEX_HOOK (inout _vt_batch any, inout d_id any)
</pre>
</div>
<a name="" />
<div class="example">
<div class="exampletitle">Free Text</div>
<div>
<pre class="programlisting">
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;
</pre>
</div>
<p>
test the text index:
</p>
<div>
<pre class="programlisting">
select id from fth where contains (dt, 'abc');
select id from fth where contains (dt, 'one');
</pre>
</div>
<p>
Both select statements will return 1 because the content is concatenated with an additional column.
</p>
<p>
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.
</p>
</div>
<div>
<pre class="programlisting">
<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)
</pre>
</div>
<p>
Table for text indexes system information
</p>
<div>
<pre class="programlisting">
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))
</pre>
</div>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="ftperformance.html" title="Performance">Previous</a>
<br />Performance</td>
<td align="center" width="34%">
<a href="freetext.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="tpcc.html" title="TPC C Benchmark Kit">Next</a>
<br />Contents of TPC C Benchmark Kit</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
768
