TEXT Indexing Elements

This chapter describes elements related to Tibero TEXT indexes.

Tibero TEXT indexes can be configured with the following preference classes:

PREFERENCE Class	Description
STORAGE	Storage of an index and a table, which are created when a TEXT index is created.
LEXER	Lexer used for the TEXT index.
WORDLIST	Token created for the TEXT index.
STOPLIST	Words which are excluded from TEXT indexes.
DATASTORE	Datastore that specifies where your TEXT index is stored.
FILTER	Filtering method to index your document.
SECTION GROUP	Organizes your document into section groups.

Preferences can be created using TEXT_DDL.CREATE_PREFERENCE.

Tibero TEXT provides predefined preferences for the seven preference classes above.

Users can create new preferences using the defined preferences.

After a preference has been created, the properties of the preference can be changed using TEXT_DDL.SET_ATTRIBUTE.

STORAGE

The STORAGE class specifies the storage for an index and table which are created when a TEXT indexis created.

BASIC_STORAGE is defined in the STORAGE class and has the following properties:

Property	Description
I_TABLE_CLAUSE	Tablespace of a table that saves TEXT index token.
I_ROWID_INDEX_CLAUSE	Tablespace of an index for the ROWID column in a token table.
I_INDEX_CLAUSE	Tablespace of an index for a token column in a token table.
K_TABLE_CLAUSE	Reserved for future functionality.
P_TABLE_CLAUSE	Reserved for future functionality.
N_TABLE_CLAUSE	Reserved for future functionality.
R_TABLE_CLAUSE	Tablespace of an index for the ROWID column in a mapping table.
S_TABLE_CLAUSE	Reserved for future functionality.

---

LEXER

The LEXER class specifies the lexer to use for a TEXT index.

BASIC_LEXER

Uses whitespace or special character delimited words.

BASIC_LEXER has the following attributes:

Attribute	Description
MIXED_CASE	Option to preserve case-sensitivity of text for indexing. If set to YES, case is preserved. (Attribute: TRUE/FALSE, default value: FALSE)
BASE_LETTER	Option to ignore base letters, such as Umlaut and acute accent for indexing.If set to YES, base letters are ignored. (Attribute: TRUE/FALSE, default value: FALSE)
WHITESPACE	Option to search for a sentence or paragraph by joining with punctuation or newline characters to separate sentences or paragraphs. The default valueis space or tab. Other than the default value, user-specified whitespace characters can be recognized. (Attribute: string, default value: space or tab characters)

JAPANESE_LEXER

Used to generate tokens in Japanese text. JAPANESE_LEXER is a Japanese morphological analyzer. JAPANESE_LEXER has the following attributes.

Attribute	Description
ASCII_MIXED_CASE	Option to preserve case-sensitivity of ASCII text for indexing. If set to YES, case is preserved. (Attribute: TRUE/FALSE, Default: FALSE)
DELIMETER	Option to ignore Japanese delimiters, such as /, -, ·, etc. If set to YES, such characters are ignored. (Attribute: TRUE/FALSE, Default: FALSE)

CHINESE_LEXER

Used to generate tokens in Chinese text. CHINESE_LEXER has the following attributes.

• Only available in UTF8 and GBK character sets.

• Can use CONTEXT indexes but not CTXCAT indexes.

CHINESE_VGRAM_LEXER

Used to generate tokens in Chinese text. Unlike CHINESE_LEXER, tokens are generated based on the number of characters. Lexical meaning is not used.

CHINESE_VGRAM_LEXER has the following limitations.

• Only available in UTF8 character set.

WORLD_LEXER

Used to generate tokens from documents containing multiple languages. This automatically detects the language and generates a token. Tokens are generated based on whitespace for languages with whitespace, and they are made in 2-grams for Japanese or Chinese containing no whitespace.

WORLD_LEXER has the following limitations.

• Only available in UTF8 character sets.

• Japanese or Chinese, which generate tokens in 2-gram, do not support wildcards.

• Can use CONTEXT indexes but not CTXCAT indexes.

---

WORDLIST

The WORDLIST class specifies a token created when a TEXT index is created. BASIC_WORDLIST is defined in the WORDLIST class and has the following properties:

Attribute	Description
PREFIX_INDEX	If this is specified as YES, prefixes of words are indexed to improve the performance of searching for a prefix. (Attribute: YES/NO, Default: NO)
PREFIX_MIN_LENGTH	Minimum length of a prefix when PREFIX_INDEX is specified as YES. (Range: 1 ~ 64, Default: 1)
PREFIX_MAX_LENGTH	Maximum length of a prefix when PREFIX_INDEX is specified as YES. (Range: 1 ~ 64, Default: 64)

---

STOPLIST

The STOPLIST class specifies words which are excluded from TEXT indexes. DEFAULT_STOPLIST and EMPTY_STOPLIST are defined in the STOPLIST class.

DEFAULT_STOPLIST has basic stopwords. For details about the basic stopwords, please refer to “Appendix”.

EMPTY_STOPLIST is a STOPLIST without STOPWORD.

A new stoplist can be created using TEXT_DDL.CREATE_STOPLIST. A new stopword can be added to the list using TEXT_DDL.ADD_STOPWORD.

---

DATASTORE

The DATASTORE class specifies how your documents are stored. The DATASTORE class defines DIRECT_DATASTORE and MULTI_COLUMN_DATASTORE.

DIRECT_DATASTORE

DIRECT_DATASTORE internally stores data in text columns. Each row is indexed as a single document. DIRECT_DATASTORE has no attributes.

MULTI_COLUMN_DATASTORE

MULTI_COLUMN_DATASTORE stores data in multiple columns. Tibero combines the columns specified with MULTI_COLUMN_DATASTORE by each row and then indexes the rows.

Attribute	Description
COLUMNS	Comma-separated columns to be indexed. You can specify the columns of type VARCHAR, CLOB, NUMBER, and DATE. You can also specify column name, column expression, and PSM function. (Attribute: STRING)
DELIMITER	Delimiter to split the column text. – COLUMN_NAME_TAG: Delimits the text of each column by XML format start and end tags. Each tag has a column name. (Default) – NEWLINE: Delimits the text of each column by newline.

---

FILTER

The FILTER class specifies how to filter documents to be indexed. The FILTER class only defines NULL_FILTER.

NULL_FILTER

NULL_FILTER does not filter. It indexes general documents, HTML and XML documents without filtering. Type NULL_FILTER has no attributes.

---

SECTION GROUP

The SECTION_GROUP class divides the document structure into multiple sections. This class only defines NULL_SECTION_GROUP, which does not define a section. You can use TEXT_DDL.CREATE_SECTION_GROUP to create a new SECTION_GROUP.

---

TEXT_DDL Package

The following procedures are provided by TEXT_DDL to create and manage preferences.

Procedure	Description
ADD_SPECIAL_SECTION	Adds a SENTENCE or PARAGRAPH as a SPECIAL SECTION in a SECTION GROUP.
ADD_STOPWORD	Adds a stopword to a stoplist.
CREATE_PREFERENCE	Creates a new preference.
CREATE_SECTION_GROUP	Defines a new SECTION_GROUP.
CREATE_STOPLIST	Creates a new stoplist.
DROP_PREFERENCE	Deletes a preference.
DROP_SECTION_GROUP	Deletes SECTION_GROUP.
DROP_STOPLIST	Deletes a stoplist.
REMOVE_SECTION	Deletes a SECTION from a SECTION GROUP.
REMOVE_STOPWORD	Deletes a stopword from a stoplist.
SET_ATTRIBUTE	Specifies the attributes of a preference.

ADD_SPECIAL_SECTION

The ADD_SPECIAL_SECTION procedure adds a SENTENCE or PARAGRAPH as a SPECIAL SECTION in a SECTION GROUP. Adding a special section allows you to find a sentence or paragraph by using the WITHIN operator.

After specifying section groups containing special sections, the delimiter specified when creating indexes tokenizes special sections.

Sentences are delimited by [word][punctuation][whitespace] or [word][punctuation][newline]. Paragraphs are delimited by [word][punctuation][newline][whitespace] or [word][punctuation][newline][newline].

Note

The default punctuation value is [. / ! / ?], andthe default newline value is [\n / \r]. The user cannot modify these punctuation and newline values.

Prototype

ADD_SPECIAL_SECTION (
    group_name	        IN VARCHAR2, 
    section_name	IN VARCHAR2)

Parameter

Parameter	Description
group_name	Name of a SECTION GROUP to be added.
section_name	Name of a section to be added. Can be specified as 'SENTENCE' or 'PARAGRAPH'.

Example

SQL>EXEC TEXT_DDL.ADD_SPECIAL_SECTION('nullgroup','SENTENCE');

ADD_STOPWORD

The ADD_STOPWORD procedure adds a stopword to a stoplist.

Prototype

ADD_STOPWORD (
    stoplist_name     IN VARCHAR2,
    stopword          IN VARCHAR2,
    lang              IN VARCHAR2 DEFAULT 'ALL')

Parameter

Parameter	Description
stoplist_name	STOPLIST name.
stopword	Stopword to add.
lang	Language to which the stopword applies. (Unsupported)

Example

SQL>EXEC TEXT_DDL.ADD_STOPWORD('DEFAULT_STOPLIST','test_word');

CREATE_PREFERENCE

The CREATE_PREFERENCE procedure creates a new preference.

Prototype

CREATE_PREFERENCE (
    udef_pref_name     IN VARCHAR2,
    pdef_pref_name     IN VARCHAR2)

Parameter

Parameter	Description
udef_pref_name	PREFERENCE name
pdef_pref_name	Predefined Tibero TEXT preference name.

Example

SQL>EXEC TEXT_DDL.CREATE_PREFERENCE('TEST1','BASIC_WORDLIST');

CREATE_SECTION_GROUP

The CREATE_SECTION_GROUP procedure creates a new section group.

Prototype

CREATE_SECTION_GROUP (
    udef_pref_name         IN VARCHAR2,
    pdef_pref_name         IN VARCHAR2)

Parameter

Parameter	Description
udef_pref_name	Preference name.
pdef_pref_name	Predefined Tibero TEXT preference name.

Example

SQL>EXEC TEXT_DDL.CREATE_SECTION_GROUP('my_sg','NULL_SECTION_GROUP');

CREATE_STOPLIST

The CREATE_STOPLIST procedure creates a new stoplist.

Prototype

CREATE_STOPLIST (
    stoplist_name         IN VARCHAR2,
    stoplist_type         IN VARCHAR2 DEFAULT 'DEFAULT_STOPLIST')

Parameter

Parameter	Description
stoplist_name	Stoplist name.
stoplist_type	Stoplist type. Currently, DEFAULT_STOPLIST and EMPTY_STOPLIST are supported.

Example

SQL>EXEC TEXT_DDL.CREATE_STOPLIST('new_stoplist', 'DEFAULT_STOPLIST');

DROP_PREFERENCE

The DROP_STOPLIST procedure deletes a stoplist.

Prototype

DROP_PREFERENCE (udef_pref_name IN VARCHAR2);

Parameter

Parameter	Description
udef_pref_name	Preference name.

Example

SQL>EXEC TEXT_DDL.DROP_PREFERENCE('TEST1');

DROP_SECTION_GROUP

The DROP_PREFERENCE procedure deletes a preference.

Prototype

DROP_SECTION_GROUP (udef_pref_name IN VARCHAR2);

Parameter

Parameter	Description
udef_pref_name	Section group name.

Example

SQL>EXEC TEXT_DDL.DROP_PREFERENCE('my_sg');

DROP_STOPLIST

The DROP_STOPLIST procedure deletes a stoplist.

Prototype

DROP_STOPLIST (stoplist_name IN VARCHAR2);

Parameter

Parameter	Description
stoplist_name	Stoplist to delete.

Example

SQL>EXEC TEXT_DDL.DROP_STOPLIST('new_stoplist');

REMOVE_SECTION

Deletes a SECTION from a SECTION GROUP

Prototype

REMOVE_SECTION (
    group_name         IN VARCHAR2,
    section_name       IN VARCHAR2)

Parameter

Parameter	Description
group_name	Name of a SECTION GROUP to be deleted.
section_name	Name of a SECTION to be deleted.

Example

SQL>EXEC TEXT_DDL.REMOVE_SECTION('nullgroup','test_section'); 
SQL>EXEC TEXT_DDL.REMOVE_SECTION('nullgroup','SENTENCE');

REMOVE_STOPWORD

The REMOVE_STOPWORD procedure deletes a stopword from a stoplist.

Prototype

REMOVE_STOPWORD (
    stoplist_name         IN VARCHAR2, 
    stopword              IN VARCHAR2,
    lang                  IN VARCHAR2 DEFAULT 'ALL' )

Parameter

Parameter	Description
stoplist_name	Name of a stoplist which includes the stopword to be deleted.
stopword	Stopword to delete.
lang	Language to which the stopword applies. (Unsupported)

Example

SQL>EXEC TEXT_DDL.REMOVE_STOPWORD('DEFAULT_STOPLIST','test_word');

SET_ATTRIBUTE

The SET_ATTRIBUTE procedure specifies the attributes for a preference.

Prototype

SET_ATTRIBUTE (
    udef_pref_name	IN VARCHAR2,
    attribute_name	IN VARCHAR2,
    value	        IN VARCHAR2)

Parameter

Parameter	Description
udef_pref_name	Preference to specify the attribute for.
attribute_name	Attribute name.
value	Attribute value.

Example

SQL>EXEC TEXT_DDL.SET_ATTRIBUTE('TEST1','PREFIX_INDEX','TRUE');

---

CTX_DDL Package

The following are procedures provided by CTX_DDL to manage CONTEXT indexes.

OPTIMIZE_INDEX

Optimizes synchronized indexes. This improves query performance because old data is removed.

Prototype

CTX_DDL.OPTIMIZE_INDEX (
        idx_name           IN VARCHAR2,
        optlevel           IN VARCHAR2,
        maxtime            IN NUMBER DEFAULT NULL,
        token              IN VARCHAR2 DEFAULT NULL,
        part_name          IN VARCHAR2 DEFAULT NULL,
        token_type         IN NUMBER DEFAULT NULL,

        parallel_degree    IN NUMBER DEFAULT 1)

Parameter

Parameter	Description
idx_name	Index name.
optlevel	One of the following two optimization methods. – FAST: combine duplicate tokens, but do not remove old (removed row) data. – FULL: combine duplicate tokens, and then remove the old (removed row) data.
maxtime	Maximum optimization time. (Unsupported)
token	Token to be optimized. (Unsupported)
part_name	Partition name for local indexes.
token_type	Token type. (Unsupported)
parallel_degree	Parallel degree. (Unsupported)

Example

begin
    ctx_ddl.optimize_index('myindex','FAST');
end

SYNC_INDEX

Synchronizes CONTEXT indexes to process inserts, updates, and deletes to the base table.

Prototype

CTX_DDL.SYNC_INDEX (
    idx_name                  IN VARCHAR2 DEFAULT NULL,
    memory                    IN VARCHAR2 DEFAULT NULL,
    part_name                 IN VARCHAR2 DEFAULT NULL,
    parallel_degree           IN NUMBER DEFAULT 1,
    maxtime                   IN NUMBER DEFAULT NULL,
    locking                   IN NUMBER DEFAULT LOCK_WAIT)

Parameter

Parameter	Description
idx_name	Index name.
memory	Runtime memory to use for synchronization. (Unsupported)
part_name	Partition name for local indexes.
parallel_degree	Parallel degree. (Unsupported)
maxtime	Maximum synchronization time. (Unsupported)
locking	How to deal with the case where another synchronization is already running in the same index or partition. (Unsupported)

Example

begin
    ctx_ddl.sync_index('myindex');
end

---

CTX_OUTPUT Package

The following are procedures provided by CTX_OUTPUT to manage CONTEXT indexes.

END_LOG

Stops logging for indexing.

Prototype

CTX_OUTPUT.END_LOG;

Example

begin
    ctx_output.end_log;
end

START_LOG

Starts logging for indexing.

Prototype

CTX_OUTPUT.START_LOG (
    file_name             IN VARCHAR2,
    write_option          IN BOOLEAN DEFAULT TRUE)

Parameter

Parameter	Description
file_name	Name of the log file.
write_option	If the log file already exists, this parameter specifies whether you overwrite the file or add a new log file.

Example

begin
    ctx_output.start_log('myindex', true);
end