BASIC_WORDLIST
Use BASIC_WORDLIST to enable stemming and fuzzy matching or to create prefix indexes with Text indexes.
Table 37 BASIC_WORDLIST Attributes
stemmer
Specify the stemmer used for word stemming in Text queries. When you do not specify a value for STEMMER, the default is ENGLISH.
Specify AUTO for the system to automatically set the stemming language according to the language setting of the database session. If the database language is American or English, then the ENGLISH stemmer is automatically used. Otherwise, the stemmer that maps to the database session language is used.
When there is no stemmer for a language, the default is NULL. With the NULL stemmer, the stem operator is ignored in queries.
You can create your own stemming user-dictionary.
Note: The STEMMER attribute of BASIC_WORDLIST preference is ignored if the INDEX_STEMS attribute of the AUTO_LEXER preference is set to YES. In this case, the same stemmer that is used by AUTO_LEXER during indexing is used to determine the stem of the query term during query.
fuzzy_match
Sspecify which fuzzy matching routines are used for the column. Fuzzy matching is currently supported for English, Japanese, and, to a lesser extent, the Western European languages.
Note: The fuzzy_match attributes value for Chinese and Korean are dummy attribute values that prevent the English and Japanese fuzzy matching routines from being used on Chinese and Korean text.
The default for fuzzy_match is GENERIC.
Specify AUTO for the system to automatically set the fuzzy matching language according to language setting of the session.
fuzzy_score
Specify a default lower limit of fuzzy score. Specify a number between 1 and 80. Text with scores below this number are not returned. The default is 60.
Fuzzy score is a measure of how close the expanded word is to the query word. The higher the score the better the match. Use this parameter to limit fuzzy expansions to the best matches.
fuzzy_numresults
Specify the maximum number of fuzzy expansions. Use a number between 0 and
- The default is 100.
Setting a fuzzy expansion limits the expansion to a specified number of the best matching words.
substring_index
Specify TRUE for Oracle Text to create a substring index. A substring index improves performance for left-truncated or double-truncated wildcard queries such as %ing or %benz%. The default is false.
Limitations of substring_index:
Oracle recommends using the wildcard_index attribute over substring_index. See “wildcard_index”. Substring indexing has the following impact on indexing and disk resources:
-
Index creation and DML processing is up to 4 times slower.
-
Index creation with
substring_indexenabled requires more rollback segments during index flushes than withsubstring_indexoff. Do either of the following when creating a substring index:-
Make available double the usual rollback.
-
Decrease the index memory to reduce the size of the index flushes to disk.
-
prefix_index
Specify yes to enable prefix indexing. Prefix indexing improves performance for right truncated wildcard searches such as TO%. Default is NO.
Note: Enabling prefix indexing increases index size.
Prefix indexing chops up tokens into multiple prefixes to store in the $I table. For example, words TOKEN and TOY are normally indexed as follows in the $I table:
| Token | Type | Information |
|---|---|---|
| TOKEN | 0 | DOCID 1 POS 1 |
| TOY | 0 | DOCID 1 POS 3 |
With prefix indexing, Oracle Text indexes the prefix substrings of these tokens as follows with a new token type of 6:
| Token | Type | Information |
|---|---|---|
| TOKEN | 0 | DOCID 1 POS 1 |
| TOY | 0 | DOCID 1 POS 3 |
| T | 6 | DOCID 1 POS 1 POS 3 |
| TO | 6 | DOCID 1 POS 1 POS 3 |
| TOK | 6 | DOCID 1 POS 1 |
| TOKE | 6 | DOCID 1 POS 1 |
| TOKEN | 6 | DOCID 1 POS 1 |
| TOY | 6 | DOCID 1 POS 3 |
Wildcard searches such as TO% are now faster because Oracle Text does no expansion of terms and merging of result sets. To obtain the result, Oracle Text need only examine the (TO,6) row.
prefix_min_length
Specify the minimum length of indexed prefixes. Default is 1.
For example, setting prefix_min_length to 3 and prefix_max_length to 5 indexes all prefixes between 3 and 5 characters long.
Note: A wildcard search whose pattern is below the minimum length or above the maximum length is searched using the slower method of equivalence expansion and merging.
prefix_max_length
Specify the maximum length of indexed prefixes. Default is 64.
For example, setting prefix_min_length to 3 and prefix_max_length to 5 indexes all prefixes between 3 and 5 characters long.
Note: A wildcard search whose pattern is below the minimum length or above the maximum length is searched using the slower method of equivalence expansion and merging.
wildcard_maxterms
Specify the maximum number of terms in a wildcard (%) expansion. Use this parameter to keep wildcard query performance within an acceptable limit. When the wildcard query expansion exceeds this number, Oracle Text returns the following error:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-20000: Oracle Text error:
DRG-51030: wildcard query expansion resulted in too many termsIn such cases, use a more restrictive query so that it results in fewer matches or increase the value of wildcard_maxterms. You can also set wildcard_maxterms to 0 to ignore the limit.
Note: If the value of wildcard_maxterms is set as 0, the query might fail and returns the above error again if too many terms are matched by the wildcard search term.
You can also capture the above error and display your own less terse message.
Note: Search terms with wildcard queries having only the wildcard character, for example: %, %_%, and %_, are threaded as stopwords.
Note: wildcard_maxterms is independent of the new WILDCARD_INDEX option. wildcard_maxterms can be set even if WILDCARD_INDEX is not used.
ndata_base_letter
Specify whether characters that have diacritical marks (umlauts, cedillas, acute accents, and so on) are converted to their base form before being stored in the Text index or queried by the NDATA operator. The default is FALSE (base-letter conversion disabled).
ndata_alternate_spelling
Specify whether to enable alternate spelling for German, Danish, and Swedish. Enabling alternate spelling allows you to index NDATA section data and query using the NDATA operator in alternate form.
When ndata_base_letter is enabled at the same time as ndata_alternate_spelling, NDATA section data is serially transformed first by alternate spelling and then by base lettering.
ndata_thesaurus
Specify a name of the thesaurus used for alternate name expansion. The indexing engine expands names in documents using synonym rings in the thesaurus. A user should make use of homographic disambiguating feature of the thesaurus to distinguish common nicknames.
An example is:
Albert
SYN Al
SYN Bert
Alfred
SYN Al
SYN FredA simple definition such as the above will put Albert, Alfred, Al, Bert, and Fred into the same synonym ring. This will cause an unexpected expansion such that the expansion of Bert includes Fred. To prevent this, you can use homographic disambiguation as in:
Albert
SYN Al (Albert)
SYN Bert (Albert)
Alfred
SYN Al (Alfred)
SYN Fred (Alfred)This forms two synonym rings, Albert-Al-Bert and Alfred-Al-Fred. Thus, the expansion of Bert no longer includes Fred. A more detailed example is:
begin
ctx_ddl.create_preference('NDAT_PREF', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('NDATA_PREF', 'NDATA_ALTERNATE_SPELLING', 'FALSE');
ctx_ddl.set_attribute('NDATA_PREF', 'NDATA_BASE_LETTER', 'TRUE');
ctx_ddl.set_attribute('NDATA_PREF', 'NDATA_THESAURUS', 'NICKNAMES');
end;Note: A sample thesaurus for names can be found in the $ORACLE_HOME/ctx/sample/thes directory. This file is dr0thsnames.txt.
ndata_join_particles
Specify a list of colon-separated name particles that can be joined with a name that follows them. A name particle, such as da, is written separately from or joined with its following name like da Vinci or daVinci. The indexing engine generates index data for both separated and join versions of a name when it finds a name particle specified in this preference. The same happens in the query processing for better recall.
reverse_index
Reverse index allows for fast searches on left-truncated search terms.
Indexed words are stored in the token table ($I) which has an index ($X) on it. Normally, if a search term such as “%xxx” is used in a query, the $X index cannot be used. So, a full table scan of the $I table is necessary, which can lead to poor search performance.
Setting REVERSE_INDEX to TRUE creates an extra index ($V) on a reverse form of the tokens. This allows for indexed lookups for left-truncated terms, leading to much better query performance for such terms.
REVERSE_INDEX speeds up searching of tokens with leading wildcards such as the second word in the search “oracle %base”. If the token has both leading and trailing wildcards such as “oracle %bas%” this attribute will not help and the SUBSTRING_INDEX option should be used instead.
Specify the attribute as a part of the wordlist preference and set it to TRUE or FALSE. Default is FALSE. Set this attribute using CTX_DDL.SET_ATTRIBUTE procedure or using ALTER INDEX REBUILD statement as used in any wordlist preference.
Syntax
ctx_ddl.set_attribute(worlist_pref_name, 'REVERSE_INDEX', BOOLEAN);worlist_pref_name
Specify the first argument as the wordlist preference name.
REVERSE_INDEX
Specify the wordlist preference name as REVERSE_INDEX.
BOOLEAN
The attribute can be set to TRUE or FALSE. By default, the value is FALSE.
The following example creates a wordlist preference and sets REVERSE_INDEX to TRUE :
exec ctx_ddl.create_preference('wrdlst', 'BASIC_WORDLIST');
exec ctx_ddl.set_attribute('wrdlst', 'REVERSE_INDEX', 'TRUE');The following traces are added for the Reverse Index $V which can be used to track timing and usage of this index at query time.
| Trace ID | Trace Name | Description |
|---|---|---|
| 37 | TRACE_QRY_VV_TIME | Time spent in executing the $V cursor |
| 38 | TRACE_QRY_VF_TIME | Time spent in fetching rows from $V |
| 39 | TRACE_QRY_V_ROWS | Number of rows with $V fetched metadata |
Wildcard indexing supports fast and efficient wildcard search for all wildcard expressions. It is set using CTX_DDL.SET_ATTRIBUTE procedure.
Setting the WILDCARD_INDEX to TRUE enables wildcard indexing.
Syntax
ctx_ddl.set_attribute(<wordlist_pref_name>, 'WILDCARD_INDEX', BOOLEAN);wordlist_pref_name
Specify the first argument as the wordlist preference name.
WILDCARD_INDEX
Specify the wordlist preference name as WILDCARD_INDEX.
BOOLEAN
The attribute can be set to TRUE or FALSE.
The following example creates a wordlist preference and sets WILDCARD_INDEX to TRUE:
begin
ctx_ddl.create_preference('mywordlist','BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','WILDCARD_INDEX','TRUE');
end;Optimization of Wildcard Index
WILDCARD_INDEX can be optimized either as part of full optimize or as part of section type optimize.
The following two examples are ways of optimizing a wildcard index:
begin
ctx_ddl.optimize_index('idx','FULL');
end;
begin
ctx_ddl.optimize_index('idx','TOKEN_TYPE',section_type=>CTX_DDL.SECTION_WILDCARD_INDEX);
end;Note: Wildcard indexing is supported for languages which only use single-byte characters.
wildcard_index_k
The WILDCARD_INDEX uses a technology known as K-grams (fixed-length substring particles). WILDCARD_INDEX_K defines the size of these grams (K). The value can range between 2 and 5. The default value is 3. Set this attribute using CTX_DDL.SET_ATTRIBUTE procedure or using ALTER INDEX REBUILD statement as used in any wordlist preference.
Note: WILDCARD_INDEX must be set to TRUE before setting WILDCARD_INDEX_K.
The following are some considerations before changing the value of K from the default value of 3:
-
Query terms that are shorter than the value of K cannot be retrieved using K-gram indexing.
-
Decreasing the value of K increases the storage requirements and increasing the value of K decreases the storage requirements.
-
Wildcard query terms must have at least K consecutive non-wildcard characters to use K-gram indexing. For example, if K value is 3, queries like “%abc%” or “%abcd%” can use K-gram indexing. For the same K value, queries like “%ab%” cannot use K-gram indexing.
-
Wildcard query terms having at least K-1 consecutive non-wildcard characters at the beginning or end of the query term, can use K-gram indexing. For example, if K value is
3, queries like “ab%” and “%ab” can use k-gram indexing.
The following example creates a wordlist preference and enables K-gram indexing with a K value of 4:
begin
ctx_ddl.create_preference('mywordlist','BASIC_WORDLIST');
ctx_ddl.set_attribute('mywordlist','WILDCARD_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','WILDCARD_INDEX_K',4);
end;Related Topics