<!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="Function: vt_create_text_index - Virtuoso Functions Guide" />
<meta name="dc.subject" content="Function: vt_create_text_index - Virtuoso Functions Guide" />
<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="functions.html" title="Chapter Contents" />
<link rel="prev" href="appendixa.html" title="Appendix" />
<link rel="next" href="functions.html#.html" title="" />
<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>Function: vt_create_text_index - Virtuoso Functions Guide</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="fn_vt_create_text_index" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>22. Virtuoso Functions Guide - vt_create_text_index</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="functions.html">Chapter Contents</a> | <a class="link" href="fn_vt_batch_update.html" title="VT_BATCH_UPDATE">Prev</a> | <a class="link" href="fn_vt_drop_ftt.html" title="VT_DROP_FTT">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>
<a class="selected" href="functions.html">Virtuoso Functions Guide</a>
</div>
<br />
<div>
<a href="functions.html#admin">Administration</a>
</div>
<div>
<a href="functions.html#aggr">Aggregate Functions</a>
</div>
<div>
<a href="functions.html#array">Array Manipulation</a>
</div>
<div>
<a href="functions.html#bpel">BPEL APIs</a>
</div>
<div>
<a href="functions.html#backup">Backup</a>
</div>
<div>
<a href="functions.html#compression">Compression</a>
</div>
<div>
<a href="functions.html#cursors">Cursor</a>
</div>
<div>
<a href="functions.html#time">Date & Time Manipulation</a>
</div>
<div>
<a href="functions.html#debug">Debug</a>
</div>
<div>
<a href="functions.html#dict">Dictionary Manipulation</a>
</div>
<div>
<a href="functions.html#encoding">Encoding & Decoding</a>
</div>
<div>
<a href="functions.html#file">File Manipulation</a>
</div>
<div>
<a class="selected" href="functions.html#ft">Free Text</a>
</div>
<div class="selected">
<div>
<a href="fn_vt_batch_update.html">vt_batch_update</a>
</div>
<div>
<a href="fn_vt_drop_ftt.html">vt_drop_ftt</a>
</div>
<div>
<a href="fn_composite.html">composite</a>
</div>
<div>
<a href="fn_composite_ref.html">composite_ref</a>
</div>
<div>
<a href="fn_contains.html">contains</a>
</div>
<div>
<a href="fn_vt_batch.html">vt_batch</a>
</div>
<div>
<a href="fn_vt_batch_d_id.html">vt_batch_d_id</a>
</div>
<div>
<a href="fn_vt_batch_feed.html">vt_batch_feed</a>
</div>
<div>
<a href="fn_vt_batch_feed_offband.html">vt_batch_feed_offban...</a>
</div>
<div>
<a class="selected" href="fn_vt_create_text_index.html">vt_create_text_index</a>
</div>
<div>
<a href="fn_vt_is_noise.html">vt_is_noise</a>
</div>
</div>
<div>
<a href="functions.html#hash">Hashing / Cryptographic</a>
</div>
<div>
<a href="functions.html#ldap">LDAP</a>
</div>
<div>
<a href="functions.html#localization">Locale</a>
</div>
<div>
<a href="functions.html#mail">Mail</a>
</div>
<div>
<a href="functions.html#misc">Miscellaneous</a>
</div>
<div>
<a href="functions.html#number">Number</a>
</div>
<div>
<a href="functions.html#phrz">Phrases</a>
</div>
<div>
<a href="functions.html#rdf">RDF data</a>
</div>
<div>
<a href="functions.html#rmt">Remote SQL Data Source</a>
</div>
<div>
<a href="functions.html#repl">Replication</a>
</div>
<div>
<a href="functions.html#soap">SOAP</a>
</div>
<div>
<a href="functions.html#sql">SQL</a>
</div>
<div>
<a href="functions.html#string">String</a>
</div>
<div>
<a href="functions.html#txn">Transaction</a>
</div>
<div>
<a href="functions.html#type">Type Mapping</a>
</div>
<div>
<a href="functions.html#uddi">UDDI</a>
</div>
<div>
<a href="functions.html#udt">User Defined Types & The CLR</a>
</div>
<div>
<a href="functions.html#vad">VAD</a>
</div>
<div>
<a href="functions.html#jvmpl">Virtuoso Java PL API</a>
</div>
<div>
<a href="functions.html#bif">Virtuoso Server Extension Interface (VSEI)</a>
</div>
<div>
<a href="functions.html#ws">Web & Internet</a>
</div>
<div>
<a href="functions.html#xml">XML</a>
</div>
<div>
<a href="functions.html#XPATH">XPATH & XQUERY</a>
</div>
<br />
<div>
<a href="functionidx.html">Functions Index</a>
</div>
<br />
</div>
<div id="text">
<h2>vt_create_text_index</h2>
<div class="refpurpose">Add text index to an existing table</div>
<div class="funcsynopsis">
<span class="funcdef">
<span class="function">vt_create_text_index</span>
</span>
(<span class="paramdef">in <span class="parameter">table</span> varchar</span>,
<span class="paramdef">in <span class="parameter">col</span> varchar</span>,
<span class="paramdef">in <span class="parameter">id_col</span> varchar</span>,
<span class="paramdef">in <span class="parameter">is_xml</span> integer</span>,
<span class="paramdef">in <span class="parameter">defer_generation</span> integer</span>,
<span class="paramdef">in <span class="parameter">clustered_columns</span> any</span>,
<span class="paramdef">in <span class="parameter">use_hook_function</span> integer</span>,
<span class="paramdefoptional">[in <span class="optional">language_name</span> varchar]</span>,
<span class="paramdefoptional">[in <span class="optional">encoding_name</span> integer]</span>);
</div>
<div class="refsect1">
<div class="refsect1title">Description</div>
<p>
The vt_create_text_index procedure adds a text index to an existing table.
There can at most be one text index per table, including super tables and
subtables.
</p>
<p>
The table argument is a string naming the table. The column is the name of the column
to index. The id_col should be the name of a unique integer row identifier column. If null,
the system will either add such a column or use an existing integer primary key if one
is available. The is_xml argument, if non-0, specifies that the values of the indexed column
should be checked for XML well formedness and that the XML structure should be taken into account in indexing the
values.
</p>
<p>
Use the CREATE TEXT INDEX statement as an alternative to the vt_create_text_index function.
</p>
<p>
In order for a table to be referenced in a text index it must have
a uniquely identifying integer key. If the table in question has such a
key this can be used as the id column. If there is no such column this
procedure makes one. Using a previously existing identifier column saves
space and if that is the primary key of the table this also saves in
retrieval time.
</p>
<p>
If the table being indexed has a single part integer primary key
vt_create_text_index will automatically use this as the identifier. Note
that the zero and negative numbers may not be used as identifier values.
</p>
<p>
Creating the index will read through the table's contents and generate
the index. When the table is changed the index can either be updated after
each change or periodically, depending on the application needs. The rationale
for background maintenance of the text index is that it is up to several times
more efficient to maintain a text index in batches of several changed documents
than after each single document change. The default maintenance mode is
synchronous, meaning that each insert, delete or update of the indexed column
will be immediately reflected in the index. This mode can be set using
the <span class="computeroutput">vt_batch_update()</span> procedure.
The mode should be set to batch if there are
any massive operations on the table.
</p>
<p>
It will create two additional tables:
</p>
<div>
<pre class="screen">
<datatable>_<datacolumn>_WORDS
</pre>
</div>
<p>
and
</p>
<div>
<pre class="screen">
VTLOG_<datatable_qualifieri>_<data_table_owner>_<data_table_name>
</pre>
</div>
<p>
and two procedures:
</p>
<div>
<pre class="screen">
vt_index_<datatable_qualifier>_<data_table_owner>_<data_table_name>
(in to_delete integer)
</pre>
</div>
<p>
and
</p>
<div>
<pre class="screen">
vt_inc_index<datatable_qualifieri>_<data_table_owner>_<data_table_name> ().
</pre>
</div>
<p>
The table <datatable>_<datafield>_WORDS contains the full text index data.
</p>
<p>
The table VTLOG_<datatable>_<datafield> is an update tracking table, similar to the snapshot log table but using the key column instead of the primary key.
</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>in the transaction semantics section that there is the sync mode for the purpose of creating a text index.</p>
</div>
</div>
<div class="refsect1">
<div class="refsect1title">Parameters</div>
<div class="refsect2">
<span class="refsect2title">table – </span>
the table containing the data to index.</div>
<div class="refsect2">
<span class="refsect2title">col – </span>
the column in the data table containing the data to index (a long varchar column).</div>
<div class="refsect2">
<span class="refsect2title">id_col – </span>
an integer unique indexed column used by the free text index as a key. If Virtuoso tries
to choose such a column among the existing in the table and if it doesn't find a suitable column it
adds such a column with the name <datacolumn>_ID, fills that up and makes an unique index on it.</div>
<div class="refsect2">
<span class="refsect2title">is_xml – </span>
if greater than 0 installs two additional triggers before insert and before update on the data table
to ensure the data being inserted into it are valid XML documents.</div>
<div class="refsect2">
<span class="refsect2title">defer_generation – </span>
if nonzero then the free-text index will not be filled by actual data
immediately after the creation. It will remain empty until explicit request for
"incremental indexing".</div>
<div class="refsect2">
<span class="refsect2title">clustered_columns – </span>
a vector of names of "clustered columns" or NULL to not store such data in the index.</div>
<div class="refsect2">
<span class="refsect2title">use_hook_function – </span>
if nonzero, two user-defined Virtuoso/PL functions will be
called when free-text data are updated, not the default system routine.
These functions are recognized by their special names:
<datatable>_<datafield>_INDEX_HOOK will be called to index
new documents and
<datatable>_<datafield>_UNINDEX_HOOK will be called to
remove obsolete index information about deleted documents.</div>
<div class="refsect2">
<span class="refsect2title">language_name – </span>
the name of the language that is used for building the index.
If the parameter is omitted or is equal to '*ini*' string, indexing routines
will use the language specified in server's configuration.</div>
<div class="refsect2">
<span class="refsect2title">encoding_name – </span>
the name of the encoding that is used by default to
index source texts.
If the parameter is omitted or is equal to '*ini*' string, indexing routines
will use the encoding specified by charset of the RDBMS connection that is
in use when the index is created.</div>
</div>
<div class="refsect1">
<div class="refsect1title">Return Types</div>
<p />
</div>
<div class="refsect1">
<div class="refsect1title">Examples</div>
<a name="ex_vt_create_text_index" />
<div class="example">
<div class="exampletitle">Creating A Text Index Using FT functions</div>
<div>
<pre class="programlisting">
create table xml_text (xt_id integer, xt_file varchar,
xt_text long varchar, primary key (xt_id));
create index xt_file on xml_text (xt_file);
vt_create_text_index ('xml_text',
'xt_text', null, 1);
</pre>
</div>
<p>-- create a text index on xt_text with XML well formedness checking on</p>
<div>
<pre class="programlisting">
VT_INDEX_DB_DBA_XML_TEXT (0);
</pre>
</div>
<p>-- populate the text index based in the data in the table.</p>
<div>
<pre class="programlisting">
VT_inc_INDEX_DB_DBA_XML_TEXT ();
</pre>
</div>
<p>
-- refresh the index to reflect changes to the data since the last call
</p>
<p>-- to this function or the initial call to VT_INDEX_DB_DBA_XML_TEXT.
</p>
<div>
<pre class="programlisting">
vt_batch_update ('DB.DBA.XML_TEXT', 'on', 10);
</pre>
</div>
<p>-- sets the index maintenance to be asynchronous, refreshed at maximum intervals
of 10 minutes or at every scheduler cycle if the latter is longer than 10 minutes.
</p>
<div>
<pre class="programlisting">
vt_batch_update ('DB.DBA.XML_TEXT', 'off', null);
</pre>
</div>
<p>
-- sets the index maintenance to be synchronous, within the same transaction
</p>
<p>
-- as the operation changing the indexed data.
</p>
</div>
</div>
</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
>
681
