This chapter provides reference information for using the CTX_DDL
PL/SQL package to create and manage the preferences, section groups, and stoplists required for Text indexes.
CTX_DDL
contains the following stored procedures and functions:
Name | Description |
---|---|
ADD_ATTR_SECTION | Adds an attribute section to a section group. |
ADD_FIELD_SECTION | Creates a filed section and assigns it to the specified section group |
ADD_INDEX | Adds an index to a catalog index preference. |
ADD_MDATA | Changes the MDATA value of a document |
ADD_MDATA_SECTION | Adds an MDATA metadata section to a document |
ADD_SPECIAL_SECTION | Adds a special section to a section group. |
ADD_STOPCLASS | Adds a stopclass to a stoplist. |
ADD_STOP_SECTION | Adds a stop section to an automatic section group. |
ADD_STOPTHEME | Adds a stoptheme to a stoplist. |
ADD_STOPWORD | Adds a stopword to a stoplist. |
ADD_SUB_LEXER | Adds a sub-lexer to a multi-lexer preference. |
ADD_ZONE_SECTION | Creates a zone section and adds it to the specified section group. |
COPY_POLICY | Creates a copy of a policy |
CREATE_INDEX_SET | Creates an index set for CTXCAT index types. |
CREATE_POLICY | Create a policy to use with ORA:CONTAINS(). |
CREATE_PREFERENCE | Creates a preference in the Text data dictionary |
CREATE_SECTION_GROUP | Creates a section group in the Text data dictionary |
CREATE_STOPLIST | Creates a stoplist. |
DROP_INDEX_SET | Drops an index set. |
DROP_POLICY | Drops a policy. |
DROP_PREFERENCE | Deletes a preference from the Text data dictionary |
DROP_SECTION_GROUP | Deletes a section group from the Text data dictionary |
DROP_STOPLIST | Drops a stoplist. |
OPTIMIZE_INDEX | Optimize the index. |
REMOVE_INDEX | Removes an index from a CTXCAT index preference. |
REMOVE_MDATA | Removes MDATA values from a document |
REMOVE_SECTION | Deletes a section from a section group |
REMOVE_STOPCLASS | Deletes a stopclass from a section group. |
REMOVE_STOPTHEME | Deletes a stoptheme from a stoplist. |
REMOVE_STOPWORD | Deletes a stopword from a section group. |
REPLACE_INDEX_METADATA | Replaces metadata for local domain indexes |
SET_ATTRIBUTE | Sets a preference attribute. |
SYNC_INDEX | Synchronize index. |
UNSET_ATTRIBUTE | Removes a set attribute from a preference. |
UPDATE_POLICY | Updates a policy. |
Adds an attribute section to an XML section group. This procedure is useful for defining attributes in XML documents as sections. This enables you to search XML attribute text with the WITHIN
operator.
Note: When you useAUTO_SECTION_GROUP , attribute sections are created automatically. Attribute sections created automatically are named in the form tag@attribute. |
CTX_DDL.ADD_ATTR_SECTION( group_name in varchar2, section_name in varchar2, tag in varchar2);
Specify the name of the XML section group. You can add attribute sections only to XML section groups.
Specify the name of the attribute section. This is the name used for WITHIN
queries on the attribute text.
The section name you specify cannot contain the colon (:), comma (,), or dot (.) characters. The section name must also be unique within group_name. Section names are case-insensitive.
Attribute section names can be no more than 64 bytes long.
Specify the name of the attribute in tag@attr form. This parameter is case-sensitive.
Consider an XML file that defines the BOOK tag with a TITLE attribute as follows:
<BOOK TITLE="Tale of Two Cities"> It was the best of times. </BOOK>
To define the title attribute as an attribute section, create an XML_SECTION_GROUP
and define the attribute section as follows:
begin ctx_ddl.create_section_group('myxmlgroup', 'XML_SECTION_GROUP'); ctx_ddl.add_attr_section('myxmlgroup', 'booktitle', 'BOOK@TITLE'); end;
When you define the TITLE
attribute section as such and index the document set, you can query the XML attribute text as follows:
'Cities within booktitle'
Creates a field section and adds the section to an existing section group. This enables field section searching with the WITHIN operator.
Field sections are delimited by start and end tags. By default, the text within field sections are indexed as a sub-document separate from the rest of the document.
Unlike zone sections, field sections cannot nest or overlap. As such, field sections are best suited for non-repeating, non-overlapping sections such as TITLE
and AUTHOR
markup in email- or news-type documents.
Because of how field sections are indexed, WITHIN queries on field sections are usually faster than WITHIN
queries on zone sections.
CTX_DDL.ADD_FIELD_SECTION( group_name in varchar2, section_name in varchar2, tag in varchar2, visible in boolean default FALSE );
Specify the name of the section group to which section_name is added. You can add up to 64 field sections to a single section group. Within the same group, section zone names and section field names cannot be the same.
Specify the name of the section to add to the group_name. You use this name to identify the section in queries. Avoid using names that contain non-alphanumeric characters such as _, since these characters must be escaped in queries. Section names are case-insensitive.
Within the same group, zone section names and field section names cannot be the same. The terms Paragraph and Sentence are reserved for special sections.
Section names need not be unique across tags. You can assign the same section name to more than one tag, making details transparent to searches.
Specify the tag which marks the start of a section. For example, if the tag is <H1>, specify H1. The start tag you specify must be unique within a section group.
If group_name is an HTML_SECTION_GROUP
, you can create field sections for the META tag's NAME/CONTENT
attribute pairs. To do so, specify tag as meta@namevalue where namevalue is the value of the NAME
attribute whose CONTENT
attribute is to be indexed as a section. Refer to the example.
Oracle Text knows what the end tags look like from the group_type parameter you specify when you create the section group.
Specify TRUE
to make the text visible within rest of document.
By default the visible flag is FALSE
. This means that Oracle Text indexes the text within field sections as a sub-document separate from the rest of the document. However, you can set the visible flag to TRUE
if you want text within the field section to be indexed as part of the enclosing document.
Visible and Invisible Field Sections
The following code defines a section group basicgroup
of the BASIC_SECTION_GROUP
type. It then creates a field section in basicgroup
called Author
for the <A>
tag. It also sets the visible flag to FALSE
:
begin
ctx_ddl.create_section_group('basicgroup', 'BASIC_SECTION_GROUP'); ctx_ddl.add_field_section('basicgroup', 'Author', 'A', FALSE);
end;
Because the Author
field section is not visible, to find text within the Author
section, you must use the WITHIN operator as follows:
'(Martin Luther King) WITHIN Author'
A query of Martin Luther King without the WITHIN
operator does not return instances of this term in field sections. If you want to query text within field sections without specifying WITHIN
, you must set the visible flag to TRUE
when you create the section as follows:
begin ctx_ddl.add_field_section('basicgroup', 'Author', 'A', TRUE); end;
Creating Sections for <META>
Tags
When you use the HTML_SECTION _GROUP
, you can create sections for META
tags.
Consider an HTML document that has a META
tag as follows:
<META NAME="author" CONTENT="ken">
To create a field section that indexes the CONTENT
attribute for the <META NAME="author">
tag:
begin ctx_ddl.create_section_group('myhtmlgroup', 'HTML_SECTION_GROUP'); ctx_ddl.add_field_section('myhtmlgroup', 'author', 'META@AUTHOR'); end
After indexing with section group mygroup
, you can query the document as follows:
'ken WITHIN author'
Nested Sections
Field sections cannot be nested. For example, if you define a field section to start with <TITLE>
and define another field section to start with <FOO>
, the two sections cannot be nested as follows:
<TITLE> dog <FOO> cat </FOO> </TITLE>
To work with nested section define them as zone sections.
Repeated field sections are allowed, but WITHIN
queries treat them as a single section. The following is an example of repeated field section in a document:
<TITLE> cat </TITLE> <TITLE> dog </TITLE>
The query (dog and cat) within title returns the document, even though these words occur in different sections.
To have WITHIN
queries distinguish repeated sections, define them as zone sections.
WITHIN operator in Chapter 3, "Oracle Text CONTAINS Query Operators".
"Section Group Types" in Chapter 2, " Oracle Text Indexing Elements".
Use this procedure to add a sub-index to a catalog index preference. You create this preference by naming one or more columns in the base table.
Since you create sub-indexes to improve the response time of structured queries, the column you add should be used in the structured_query
clause of the CATSEARCH operator at query-time.
CTX_DDL.ADD_INDEX(set_name in varchar2,
column_list varchar2, storage_clause varchar2);
Specify the name of the index set.
Specify a comma separated list of columns to index. At index time, any column listed here cannot have a NULL value in any row in the base table. If any row is NULL during indexing and error is raised.
You must always ensure that your columns have non-NULL values before and after indexing.
Specify a storage clause.
Consider a table called AUCTION
with the following schema:
create table auction(
item_id number, title varchar2(100), category_id number, price number, bid_close date);
Assume that queries on the table involve a mandatory text query clause and optional structured conditions on category_id
. Results must be sorted based on bid_close
.
You can create a catalog index to support the different types of structured queries a user might enter.
To create the indexes, first create the index set preference then add the required indexes to it:
begin ctx_ddl.create_index_set('auction_iset'); ctx_ddl.add_index('auction_iset','bid_close'); ctx_ddl.add_index('auction_iset','category_id, bid_close'); end;
Create the combined catalog index with CREATE
INDEX
as follows:
create index auction_titlex on AUCTION(title) indextype is CTXCAT parameters ('index set auction_iset');
To query the title column for the word pokemon, you can issue regular and mixed queries as follows:
select * from AUCTION where CATSEARCH(title, 'pokemon',NULL)> 0; select * from AUCTION where CATSEARCH(title, 'pokemon', 'category_id=99 order by bid_close desc')> 0;
VARCHAR2
columns in the column list of a CTXCAT
index of an index set cannot exceed 30 bytes.
Use this procedure to change the metadata of a document that has been specified as an MDATA
section. After this call, MDATA
queries involving the named MDATA
value will find documents with the given MDATA
value.
There are two versions of CTX_DDL.ADD_MDATA
: one for adding a single metadata value to a single rowid, and one for handing multiple values, multiple rowids, or both.
CTX_DDL.ADD_MDATA
is transactional; it takes effect immediately in the calling session, can be seen only in the calling session, can be reversed with a ROLLBACK
command, and must be committed to take permanent effect.
Use CTX_DDL.REMOVE_MDATA to remove metadata values from already-indexed documents. Only the owner of the index is allowed to call ADD_MDATA
and REMOVE_MDATA
.
This is the syntax for adding a single value to a single rowid:
CTX_DDL.ADD_MDATA( idx_name IN VARCHAR2, section_name IN VARCHAR2, mdata_value IN VARCHAR2, mdata_rowid IN VARCHAR2, [part_name] IN VARCHAR2] );
Name of the text index that contains the named rowid.
Name of the MDATA
section.
The metadata value to add to the document.
The rowid to which to add the metadata value.
Name of the index partition, if any. Must be provided for local partitioned indexes and must be NULL for global, non-partitioned indexes.
This is the syntax for handling multiple values, multiple rowids, or both. This version is more efficient for large numbers of new values or rowids.
CTX_DDL.ADD_MDATA( idx_name IN VARCHAR2, section_name IN VARCHAR2, mdata_values SYS.ODCIVARCHAR2LIST, mdata_rowids SYS.ODCIRIDLIST, [part_name] IN VARCHAR2] );
Name of the text index that contains the named rowids.
Name of the MDATA
section.
List of metadata values. If a metadata value contains a comma, the comma must be escaped with a backslash.
rowids to which to add the metadata values.
Name of the index partition, if any. Must be provided for local partitioned indexes and must be NULL for global, non-partitioned indexes.
This example updates a single value:
select rowid from mytab where contains(text, 'MDATA(sec, value')>0; No rows returned exec ctx_ddl.add_mdata('my_index', 'sec', 'value', 'ABC'); select rowid from mytab where contains(text, 'MDATA(sec, value')>0; ROWID ----- ABC
This example updates multiple values:
begin ctx_ddl.add_mdata('my_index', 'sec', sys.odcivarchar2list('value1','value2','value3'), sys.odciridlist('ABC','DEF')); end;
This is equivalent to:
begin ctx_ddl.add_mdata('my_index', 'sec', 'value1', 'ABC'); ctx_ddl.add_mdata('my_index', 'sec', 'value1', 'DEF'); ctx_ddl.add_mdata('my_index', 'sec', 'value2', 'ABC'); ctx_ddl.add_mdata('my_index', 'sec', 'value2', 'DEF'); ctx_ddl.add_mdata('my_index', 'sec', 'value3', 'ABC'); ctx_ddl.add_mdata('my_index', 'sec', 'value3', 'DEF'); end;
If a rowid is not yet indexed, CTX_DDL.ADD.MDATA
completes without error, but an error is logged in CTX_USER_INDEX_ERRORS
.
See also "ADD_MDATA_SECTION"; "REMOVE_MDATA"; "MDATA"; as well as the Section Searching chapter of the Oracle Text Application Developer's Guide.
Use this procedure to add an MDATA
section, with an accompanying value, to an existing section group. MDATA
sections cannot be added to Null Section groups, Path Section groups, or Auto Section groups.
Section values undergo a simplified normalization:
Leading and trailing whitespace on the value is removed.
The value is truncated to 64 bytes.
The value is converted to upper case.
The value is indexed as a single value; if the value consists of multiple words, it is not broken up.
Case is preserved. If the document is dynamically generated, you can implement case-insensitivity by uppercasing MDATA
values and making sure to search only in uppercase.
Use CTX_DDL.REMOVE_SECTION to remove sections.
CTX_DDL.ADD_MDATA_SECTION( group_name IN VARCHAR2, section_name IN VARCHAR2, tag IN VARCHAR2, );
Name of the section group that will contain the MDATA
section.
Name of the MDATA
section.
The value of the MDATA
section. For example, if the section is <AUTHOR>
, the value could be Cynthia Kadohata (author of the novel The Floating World). More than one tag can be assigned to a given MDATA
section.
This example creates an MDATA
section called AUTHOR
and gives it the value Gordon Burn (author of the novel Alma).
ctx_ddl.create.section.group('htmgroup', 'HTML_SECTION_GROUP'); ctx_ddl.add_mdata_section('htmgroup', 'author', 'Gordon Burn');
See also "ADD_MDATA"; "REMOVE_MDATA"; "MDATA"; "CREATE_SECTION_GROUP", as well as the Section Searching chapter of the Oracle Text Application Developer's Guide.
Adds a special section, either SENTENCE
or PARAGRAPH
, to a section group. This enables searching within sentences or paragraphs in documents with the WITHIN operator.
A special section in a document is a section which is not explicitly tagged like zone and field sections. The start and end of special sections are detected when the index is created. Oracle Text supports two such sections: paragraph and sentence.
The sentence and paragraph boundaries are determined by the lexer. For example, the lexer recognizes sentence and paragraph section boundaries as follows:
Table 7-1 Paragraph and Sentence Section Boundaries
Special Section | Boundary |
---|---|
SENTENCE | WORD/PUNCT/WHITESPACE |
WORD/PUNCT/NEWLINE | |
PARAGRAPH | WORD/PUNCT/NEWLINE/WHITESPACE (indented paragraph) |
WORD/PUNCT/NEWLINE/NEWLINE (block paragraph) |
The punctuation, whitespace, and newline characters are determined by your lexer settings and can be changed.
If the lexer cannot recognize the boundaries, no sentence or paragraph sections are indexed.
CTX_DDL.ADD_SPECIAL_SECTION( group_name IN VARCHAR2, section_name IN VARCHAR2);
Specify the name of the section group.
Specify SENTENCE
or PARAGRAPH
.
The following code enables searching within sentences within HTML documents:
begin ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP'); ctx_ddl.add_special_section('htmgroup', 'SENTENCE'); end;
You can also add zone sections to the group to enable zone searching in addition to sentence searching. The following example adds the zone section Headline
to the section group htmgroup
:
begin ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP'); ctx_ddl.add_special_section('htmgroup', 'SENTENCE'); ctx_ddl.add_zone_section('htmgroup', 'Headline', 'H1'); end;
If you are only interested in sentence or paragraph searching within documents and not interested in defining zone or field sections, you can use the NULL_SECTION_GROUP
as follows:
begin ctx_ddl.create_section_group('nullgroup', 'NULL_SECTION_GROUP'); ctx_ddl.add_special_section('nullgroup', 'SENTENCE'); end;
WITHIN operator in Chapter 3, "Oracle Text CONTAINS Query Operators".
"Section Group Types" in Chapter 2, " Oracle Text Indexing Elements".
Adds a stopclass to a stoplist. A stopclass is a class of tokens that is not to be indexed.
CTX_DDL.ADD_STOPCLASS( stoplist_name in varchar2, stopclass in varchar2 );
Specify the name of the stoplist.
Specify the stopclass to be added to stoplist_name. Currently, only the NUMBERS
class is supported. It is not possible to create a custom stopclass.
NUMBERS
includes tokens that follow the number pattern: digits, numgroup
, and numjoin
only. Therefore, 123ABC is not a number, nor is A123. These are labeled as MIXED
. $123 is not a number (this token is not common in a text index because non-alphanumerics become whitespace by default). In the United States, 123.45 is a number, but 123.456.789 is not; in Europe, where numgroup may be '.', the reverse is true.
The maximum number of stopwords, stopthemes, and stopclasses you can add to a stoplist is 4095.
The following code adds a stopclass of NUMBERS
to the stoplist mystop
:
begin ctx_ddl.add_stopclass('mystop', 'NUMBERS'); end;
Adds a stop section to an automatic section group. Adding a stop section causes the automatic section indexing operation to ignore the specified section in XML documents.
Note: Adding a stop section causes no section information to be created in the index. However, the text within a stop section is always searchable. |
Adding a stop section is useful when your documents contain many low information tags. Adding stop sections also improves indexing performance with the automatic section group.
The number of stop sections you can add is unlimited.
Stop sections do not have section names and hence are not recorded in the section views.
CTX_DDL.ADD_STOP_SECTION( section_group IN VARCHAR2, tag IN VARCHAR2);
Specify the name of the automatic section group. If you do not specify an automatic section group, this procedure returns an error.
Specify the tag to ignore during indexing. This parameter is case-sensitive. Defining a stop tag as such also stops the tag's attribute sections, if any.
You can qualify the tag with document type in the form (doctype)tag. For example, if you wanted to make the <fluff>
tag a stop section only within the mydoc
document type, specify (mydoc)fluff
for tag.
Defining Stop Sections
The following code adds a stop section identified by the tag <fluff>
to the automatic section group myauto
:
begin ctx_ddl.add_stop_section('myauto', 'fluff'); end;
This code also stops any attribute sections contained within <fluff>
. For example, if a document contained:
<fluff type="computer">
Then the preceding code also stops the attribute section fluff@type.
Doctype Sensitive Stop Sections
The following code creates a stop section for the tag <fluff>
only in documents that have a root element of mydoc
:
begin ctx_ddl.add_stop_section('myauto', '(mydoc)fluff'); end;
ALTER INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
Adds a single stoptheme to a stoplist. A stoptheme is a theme that is not to be indexed.
In English, you query on indexed themes using the ABOUT operator.
CTX_DDL.ADD_STOPTHEME( stoplist_name in varchar2, stoptheme in varchar2 );
Specify the name of the stoplist.
Specify the stoptheme to be added to stoplist_name. The system normalizes the stoptheme you enter using the knowledge base. If the normalized theme is more than one theme, the system does not process your stoptheme. For this reason, Oracle recommends that you submit single stopthemes.
The maximum number of stopwords, stopthemes, and stopclasses you can add to a stoplist is 4095.
The following example adds the stoptheme banking
to the stoplist mystop
:
begin ctx_ddl.add_stoptheme('mystop', 'banking'); end;
ABOUT operator in Chapter 3, "Oracle Text CONTAINS Query Operators".
Use this procedure to add a single stopword to a stoplist.
To create a list of stopwords, you must call this procedure once for each word.
CTX_DDL.ADD_STOPWORD(
stoplist_name in varchar2, stopword in varchar2, language in varchar2 default NULL
);
Specify the name of the stoplist.
Specify the stopword to be added.
Language-specific stopwords must be unique across the other stopwords specific to the language. For example, it is valid to have a German die and an English die in the same stoplist.
The maximum number of stopwords, stopthemes, and stopclasses you can add to a stoplist is 4095.
Specify the language of stopword
when the stoplist you specify with stoplist_name
is of type MULTI_STOPLIST
. You must specify the Globalization Support name or abbreviation of an Oracle Text-supported language.
To make a stopword active in multiple languages, specify ALL
for this parameter. For example, defining ALL
stopwords is useful when you have international documents that contain English fragments that need to be stopped in any language.
An ALL stopword is active in all languages. If you use the multi-lexer, the language-specific lexing of the stopword occurs, just as if it had been added multiple times in multiple specific languages.
Otherwise, specify NULL
.
Single Language Stoplist
The following example adds the stopwords because, notwithstanding, nonetheless, and therefore to the stoplist mystop
:
begin
ctx_ddl.add_stopword('mystop', 'because'); ctx_ddl.add_stopword('mystop', 'notwithstanding'); ctx_ddl.add_stopword('mystop', 'nonetheless'); ctx_ddl.add_stopword('mystop', 'therefore');
end;
Multi-Language Stoplist
The following example adds the German word die to a multi-language stoplist:
begin
ctx_ddl.add_stopword('mystop', 'Die','german');
end;
Note: You can add stopwords after you create the index withALTER INDEX . |
Adding An ALL Stopword
The following adds the word the as an ALL
stopword to the multi-language stoplist globallist:
begin
ctx_ddl.add_stopword('globallist','the','ALL');
end;
ALTER INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
Add a sub-lexer to a multi-lexer preference. A sub-lexer identifies a language in a multi-lexer (multi-language) preference. Use a multi-lexer preference when you want to index more than one language.
The following restrictions apply to using CTX_DDL.ADD_SUB_LEXER
:
The invoking user must be the owner of the multi-lexer or CTXSYS
.
The lexer_name parameter must name a preference which is a multi-lexer lexer.
A lexer for default must be defined before the multi-lexer can be used in an index.
The sub-lexer preference owner must be the same as multi-lexer preference owner.
The sub-lexer preference must not be a multi-lexer lexer.
A sub-lexer preference cannot be dropped while it is being used in a multi-lexer preference.
CTX_DDL.ADD_SUB_LEXER
records only a reference. The sub-lexer values are copied at create index time to index value storage.
CTX_DDL.ADD_SUB_LEXER( lexer_name in varchar2, language in varchar2, sub_lexer in varchar2, alt_value in varchar2 default null );
Specify the name of the multi-lexer preference.
Specify the Globalization Support language name or abbreviation of the sub-lexer. For example, you can specify ENGLISH
or EN
for English.
The sub-lexer you specify with sub_lexer is used when the language column has a value case-insensitive equal to the Globalization Support name of abbreviation of language.
Specify DEFAULT
to assign a default sub-lexer to use when the value of the language column in the base table is null, invalid, or unmapped to a sub-lexer. The DEFAULT
lexer is also used to parse stopwords.
If a sub-lexer definition for language already exists, then it is replaced by this call.
Specify the name of the sub-lexer to use for this language.
Optionally specify an alternate value for language.
If you specify DEFAULT
for language, you cannot specify an alt_value.
The alt_value is limited to 30 bytes and cannot be an Globalization Support language name, abbreviation, or DEFAULT
.
This example shows how to create a multi-language text table and how to set up the multi-lexer to index the table.
Create the multi-language table with a primary key, a text column, and a language column as follows:
create table globaldoc ( doc_id number primary key, lang varchar2(3), text clob );
Assume that the table holds mostly English documents, with the occasional German or Japanese document. To handle the three languages, you must create three sub-lexers, one for English, one for German, and one for Japanese:
ctx_ddl.create_preference('english_lexer','basic_lexer'); ctx_ddl.set_attribute('english_lexer','index_themes','yes'); ctx_ddl.set_attribtue('english_lexer','theme_language','english'); ctx_ddl.create_preference('german_lexer','basic_lexer'); ctx_ddl.set_attribute('german_lexer','composite','german'); ctx_ddl.set_attribute('german_lexer','mixed_case','yes'); ctx_ddl.set_attribute('german_lexer','alternate_spelling','german'); ctx_ddl.create_preference('japanese_lexer','japanese_vgram_lexer');
Create the multi-lexer preference:
ctx_ddl.create_preference('global_lexer', 'multi_lexer');
Since the stored documents are mostly English, make the English lexer the default:
ctx_ddl.add_sub_lexer('global_lexer','default','english_lexer');
Add the German and Japanese lexers in their respective languages. Also assume that the language column is expressed in ISO 639-2, so we add those as alternate values.
ctx_ddl.add_sub_lexer('global_lexer','german','german_lexer','ger'); ctx_ddl.add_sub_lexer('global_lexer','japanese','japanese_lexer','jpn');
Create the index globalx
, specifying the multi-lexer preference and the language column in the parameters string as follows:
create index globalx on globaldoc(text) indextype is ctxsys.context parameters ('lexer global_lexer language column lang');
Creates a zone section and adds the section to an existing section group. This enables zone section searching with the WITHIN operator.
Zone sections are sections delimited by start and end tags. The <B>
and </B>
tags in HTML, for instance, marks a range of words which are to be rendered in boldface.
Zone sections can be nested within one another, can overlap, and can occur more than once in a document.
CTX_DDL.ADD_ZONE_SECTION( group_name in varchar2, section_name in varchar2, tag in varchar2 );
Specify the name of the section group to which section_name is added.
Specify the name of the section to add to the group_name. You use this name to identify the section in WITHIN
queries. Avoid using names that contain non-alphanumeric characters such as _, since most of these characters are special must be escaped in queries. Section names are case-insensitive.
Within the same group, zone section names and field section names cannot be the same. The terms Paragraph and Sentence are reserved for special sections.
Section names need not be unique across tags. You can assign the same section name to more than one tag, making details transparent to searches.
Specify the pattern which marks the start of a section. For example, if <H1>
is the HTML tag, specify H1
for tag. The start tag you specify must be unique within a section group.
Oracle Text knows what the end tags look like from the group_type parameter you specify when you create the section group.
If group_name is an HTML_SECTION_GROUP
, you can create zone sections for the META tag's NAME/CONTENT
attribute pairs. To do so, specify tag as meta@namevalue where namevalue is the value of the NAME
attribute whose CONTENT
attributes are to be indexed as a section. Refer to the example.
If group_name is an XML_SECTION_GROUP
, you can optionally qualify tag with a document type (root element) in the form (doctype)tag. Doing so makes section_name sensitive to the XML document type declaration. Refer to the example.
The following code defines a section group called htmgroup
of type HTML_SECTION_GROUP
. It then creates a zone section in htmgroup
called headline
identified by the <H1> tag:
begin ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP'); ctx_ddl.add_zone_section('htmgroup', 'heading', 'H1'); end;
After indexing with section group htmgroup
, you can query within the heading section by issuing a query as follows:
'Oracle WITHIN heading'
Creating Sections for <META NAME>
Tags
You can create zone sections for HTML META tags when you use the HTML_SECTION_GROUP
.
Consider an HTML document that has a META
tag as follows:
<META NAME="author" CONTENT="ken">
To create a zone section that indexes all CONTENT
attributes for the META
tag whose NAME
value is author:
begin ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP'); ctx_ddl.add_zone_section('htmgroup', 'author', 'meta@author'); end
After indexing with section group htmgroup
, you can query the document as follows:
'ken WITHIN author'
Creating Document Type Sensitive Sections (XML Documents Only)
You have an XML document set that contains the <book>
tag declared for different document types (DTDs). You want to create a distinct book section for each document type.
Assume that myDTDname
is declared as an XML document type as follows:
<!DOCTYPE myDTDname> <myDTDname> ...
(Note: the DOCTYPE
must match the top-level tag.)
Within myDTDname
, the element <book>
is declared. For this tag, you can create a section named mybooksec
that is sensitive to the tag's document type as follows:
begin ctx_ddl.create_section_group('myxmlgroup', 'XML_SECTION_GROUP'); ctx_ddl.add_zone_section('myxmlgroup', 'mybooksec', '(myDTDname)book'); end;
Zone sections can repeat. Each occurrence is treated as a separate section. For example, if <H1> denotes a heading
section, they can repeat in the same documents as follows:
<H1> The Brown Fox </H1>
<H1> The Gray Wolf </H1>
Assuming that these zone sections are named Heading
, the query Brown WITHIN Heading returns this document. However, a query of (Brown and Gray) WITHIN Heading does not.
Zone sections can overlap each other. For example, if <B>
and <I>
denote two different zone sections, they can overlap in document as follows:
plain <B> bold <I> bold and italic </B> only italic </I> plain
Zone sections can nest, including themselves as follows:
<TD> <TABLE><TD>nested cell</TD></TABLE></TD>
Using the WITHIN
operator, you can write queries to search for text in sections within sections. For example, assume the BOOK1
, BOOK2
, and AUTHOR
zone sections occur as follows in documents doc1 and doc2:
doc1:
<book1> <author>Scott Tiger</author> This is a cool book to read.</book1>
doc2:
<book2> <author>Scott Tiger</author> This is a great book to read.</book2>
Consider the nested query:
'(Scott within author) within book1'
This query returns only doc1.
WITHIN operator in Chapter 3, "Oracle Text CONTAINS Query Operators".
"Section Group Types" in Chapter 2, " Oracle Text Indexing Elements".
Creates a new policy from an existing policy or index.
ctx_ddl.copy_policy( source_policy VARCHAR2, policy_name VARCHAR2 );
The name of the policy or index being copied.
The name of the new policy copy.
The preference values are copied from the source_policy
. Both the source policy or index and the new policy must be owned by the same database user.
Creates an index set for CTXCAT
index types. You name this index set in the parameter clause of CREATE
INDEX
when you create a CTXCAT
index.
CTX_DDL.CREATE_INDEX_SET(set_name in varchar2);
Specify the name of the index set. You name this index set in the parameter clause of CREATE
INDEX
when you create a CTXCAT
index.
Creates a policy to use with the CTX_DOC.POLICY_*
procedures and the ORA:CONTAINS
function. ORA:CONTAINS
is a function you use within an XPATH
query expression with existsNode()
.
CTX_DDL.CREATE_POLICY( policy_name IN VARCHAR2 DEFAULT NULL, filter IN VARCHAR2 DEFAULT NULL, section_group IN VARCHAR2 DEFAULT NULL, lexer IN VARCHAR2 DEFAULT NULL, stoplist IN VARCHAR2 DEFAULT NULL, wordlist IN VARCHAR2 DEFAULT NULL);
Specify the name for the new policy. Policy names and Text indexes share the same namespace.
Specify the filter preference to use.
Specify the section group to use. You can specify only NULL_SECTION_GROUP
. Only special (sentence and paragraph) sections are supported.
Specify the lexer preference to use. Your INDEX_THEMES
attribute must be disabled.
Specify the stoplist to use.
Specify the wordlist to use.
Create mylex lexer preference named mylex.
begin ctx_ddl.create_preference('mylex', 'BASIC_LEXER'); ctx_ddl.set_attribute('mylex', 'printjoins', '_-'); ctx_ddl.set_attribute ( 'mylex', 'index_themes', 'NO'); ctx_ddl.set_attribute ( 'mylex', 'index_text', 'YES'); end;
Create a stoplist preference named mystop.
begin ctx_ddl.create_stoplist('mystop', 'BASIC_STOPLIST'); ctx_ddl.add_stopword('mystop', 'because'); ctx_ddl.add_stopword('mystop', 'nonetheless'); ctx_ddl.add_stopword('mystop', 'therefore'); end;
Create a wordlist preference named 'mywordlist'.
begin ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('mywordlist','FUZZY_MATCH','ENGLISH'); ctx_ddl.set_attribute('mywordlist','FUZZY_SCORE','0'); ctx_ddl.set_attribute('mywordlist','FUZZY_NUMRESULTS','5000'); ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX','TRUE'); ctx_ddl.set_attribute('mywordlist','STEMMER','ENGLISH'); end;
exec ctx_ddl.create_policy('my_policy', NULL, NULL, 'mylex', 'mystop', 'mywordlist');
or
exec ctx_ddl.create_policy(policy_name => 'my_policy', lexer => 'mylex', stoplist => 'mystop', wordlist => 'mywordlist');
Then you can issue the following existsNode()
query with your own defined policy:
select id from xmltab where existsNode(doc, '/book/chapter[ ora:contains(summary,"dog or cat", "my_policy") >0 ]', 'xmlns:ora="http://xmlns.oracle.com/xdb" ')=1;
You can update your policy by doing:
exec ctx_ddl.update_policy(policy_name => 'my_policy', lexer => 'my_new_lex');
You can drop your policy by doing:
exec ctx_ddl.drop_policy(policy_name => 'my_policy');
Creates a preference in the Text data dictionary. You specify preferences in the parameter string of CREATE INDEX or ALTER INDEX.
CTX_DDL.CREATE_PREFERENCE(preference_name in varchar2, object_name in varchar2);
Specify the name of the preference to be created.
Specify the name of the preference type.
See Also: For a complete list of preference types and their associated attributes, see Chapter 2, " Oracle Text Indexing Elements". |
Creating Text-only Index
The following example creates a lexer preference that specifies a text-only index. It does so by creating a BASIC_LEXER
preference called my_lexer
with CTX_DDL.CREATE_PREFERENCE
. It then calls CTX_DDL.SET_ATTRIBUTE twice, first specifying YES for the INDEX_TEXT
attribute, then specifying NO for the INDEX_THEMES
attribute.
begin ctx_ddl.create_preference('my_lexer', 'BASIC_LEXER'); ctx_ddl.set_attribute('my_lexer', 'INDEX_TEXT', 'YES'); ctx_ddl.set_attribute('my_lexer', 'INDEX_THEMES', 'NO'); end;
The following example creates a data storage preference called mypref
that tells the system that the files to be indexed are stored in the operating system. The example then uses CTX_DDL.SET_ATTRIBUTE to set the PATH
attribute of to the directory /docs
.
begin ctx_ddl.create_preference('mypref', 'FILE_DATASTORE'); ctx_ddl.set_attribute('mypref', 'PATH', '/docs'); end;
See Also: For more information about data storage, see "Datastore Types" in Chapter 2, " Oracle Text Indexing Elements". |
Creating Master/Detail Relationship
You can use CTX_DDL.CREATE_PREFERENCE to create a preference with DETAIL_DATASTORE
. You use CTX_DDL.SET_ATTRIBUTE to set the attributes for this preference. The following example shows how this is done:
begin ctx_ddl.create_preference('my_detail_pref', 'DETAIL_DATASTORE'); ctx_ddl.set_attribute('my_detail_pref', 'binary', 'true'); ctx_ddl.set_attribute('my_detail_pref', 'detail_table', 'my_detail'); ctx_ddl.set_attribute('my_detail_pref', 'detail_key', 'article_id'); ctx_ddl.set_attribute('my_detail_pref', 'detail_lineno', 'seq'); ctx_ddl.set_attribute('my_detail_pref', 'detail_text', 'text'); end;
See Also: For more information about master/detail, see "DETAIL_DATASTORE" in Chapter 2, " Oracle Text Indexing Elements". |
The following examples specify that the index tables are to be created in the foo
tablespace with an initial extent of 1K:
begin ctx_ddl.create_preference('mystore', 'BASIC_STORAGE'); ctx_ddl.set_attribute('mystore', 'I_TABLE_CLAUSE', 'tablespace foo storage (initial 1K)'); ctx_ddl.set_attribute('mystore', 'K_TABLE_CLAUSE', 'tablespace foo storage (initial 1K)'); ctx_ddl.set_attribute('mystore', 'R_TABLE_CLAUSE', 'tablespace foo storage (initial 1K)'); ctx_ddl.set_attribute('mystore', 'N_TABLE_CLAUSE', 'tablespace foo storage (initial 1K)'); ctx_ddl.set_attribute('mystore', 'I_INDEX_CLAUSE', 'tablespace foo storage (initial 1K)'); end;
Creating Preferences with No Attributes
When you create preferences with types that have no attributes, you need only create the preference, as in the following example which sets the filter to the NULL_FILTER
:
begin ctx_ddl.create_preference('my_null_filter', 'NULL_FILTER'); end;
CREATE INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
ALTER INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
Creates a section group for defining sections in a text column.
When you create a section group, you can add to it zone, field, or special sections with ADD_ZONE_SECTION, ADD_FIELD_SECTION, ADD_MDATA_SECTION, or ADD_SPECIAL_SECTION.
When you index, you name the section group in the parameter string of CREATE INDEX or ALTER INDEX.
After indexing, you can query within your defined sections with the WITHIN operator.
CTX_DDL.CREATE_SECTION_GROUP( group_name in varchar2, group_type in varchar2 );
Specify the section group name to create as [user.]section_group_name
. This parameter must be unique within an owner.
Specify section group type. The group_type parameter can be one of:
The following command creates a section group called htmgroup
with the HTML group type.
begin
ctx_ddl.create_section_group('htmgroup', 'HTML_SECTION_GROUP');
end;
The following command creates a section group called auto
with the AUTO_SECTION_GROUP
group type to be used to automatically index tags in XML documents.
begin
ctx_ddl.create_section_group('auto', 'AUTO_SECTION_GROUP');
end;
WITHIN operator in Chapter 3, "Oracle Text CONTAINS Query Operators".
"Section Group Types" in Chapter 2, " Oracle Text Indexing Elements".
Use this procedure to create a new, empty stoplist. Stoplists can contain words or themes that are not to be indexed.
You can also create multi-language stoplists to hold language-specific stopwords. A multi-language stoplist is useful when you index a table that contains documents in different languages, such as English, German, and Japanese. When you do so, you text table must contain a language column.
You can add either stopwords, stopclasses, or stopthemes to a stoplist using ADD_STOPWORD, ADD_STOPCLASS, or ADD_STOPTHEME.
You can specify a stoplist in the parameter string of CREATE INDEX or ALTER INDEX to override the default stoplist CTXSYS.DEFAULT_STOPLIST.
CTX_DDL.CREATE_STOPLIST(
stoplist_name IN VARCHAR2, stoplist_type IN VARCHAR2 DEFAULT 'BASIC_STOPLIST');
Specify the name of the stoplist to be created.
Specify BASIC_STOPLIST
to create a stoplist for a single language. This is the default.
Specify MULTI_STOPLIST
to create a stoplist with language-specific stopwords.
At indexing time, the language column of each document is examined, and only the stopwords for that language are eliminated. At query time, the session language setting determines the active stopwords, like it determines the active lexer when using the multi-lexer.
Note: When indexing a multi-language table with a multi-language stoplist, your table must have a language column. |
Single Language Stoplist
The following code creates a stoplist called mystop
:
begin ctx_ddl.create_stoplist('mystop', 'BASIC_STOPLIST'); end;
Multi-Language Stoplist
The following code creates a multi-language stoplist called multistop
and then adds tow language-specific stopwords:
begin ctx_ddl.create_stoplist('multistop', 'MULTI_STOPLIST'); ctx_ddl.add_stopword('mystop', 'Die','german'); ctx_ddl.add_stopword('mystop', 'Or','english'); end;
CREATE INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
ALTER INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
Drops a CTXCAT
index set created with CTX_DDL.CREATE_INDEX_SET.
CTX_DDL.DROP_INDEX_SET(set_name in varchar2);
Specify the name of the index set to drop.
Dropping an index set drops all of the sub-indexes it contains.
Drops a policy created with CTX_DDL.CREATE_POLICY.
CTX_DDL.DROP_POLICY(policy_name IN VARCHAR2);
Specify the name of the policy to drop.
The DROP_PREFERENCE
procedure deletes the specified preference from the Text data dictionary. Dropping a preference does not affect indexes that have already been created using that preference.
CTX_DDL.DROP_PREFERENCE(preference_name IN VARCHAR2);
Specify the name of the preference to be dropped.
The following code drops the preference my_lexer
.
begin ctx_ddl.drop_preference('my_lexer'); end;
See also CTX_DDL.CREATE_PREFERENCE.
The DROP_SECTION_GROUP
procedure deletes the specified section group, as well as all the sections in the group, from the Text data dictionary.
CTX_DDL.DROP_SECTION_GROUP(group_name IN VARCHAR2);
Specify the name of the section group to delete.
The following code drops the section group htmgroup
and all its sections:
begin ctx_ddl.drop_section_group('htmgroup'); end;
See also CTX_DDL.CREATE_SECTION_GROUP.
Drops a stoplist from the Text data dictionary. When you drop a stoplist, you must re-create or rebuild the index for the change to take effect.
CTX_DDL.DROP_STOPLIST(stoplist_name in varchar2);
Specify the name of the stoplist.
The following code drops the stoplist mystop
:
begin ctx_ddl.drop_stoplist('mystop'); end;
See also CTX_DDL.CREATE_STOPLIST.
Use this procedure to optimize the index. You optimize your index after you synchronize it. Optimizing an index removes old data and minimizes index fragmentation, which can improve query response time. Querying and DML may proceed while optimization takes place.
You can optimize in fast, full, rebuild, token, or token-type mode.
Fast mode compacts data but does not remove rows.
Full mode compacts data and removes rows.
Optimize in rebuild mode rebuilds the $I
table (the inverted list table) in its entirety. Rebuilding an index is often significantly faster than performing a full optimization, and is more likely to result in smaller indexes, especially if the index is heavily fragmented.
Rebuild optimization creates a more compact copy of the $I
table, and then switches the original $I
table and the copy. The rebuild operation will therefore require enough space to store the copy as well as the original. (If redo logging is enabled, then additional space is required in the redo log as well.) At the end of the rebuild operation, the original $I
table is dropped, and the space can be reused.
In token mode, you specify a specific token to be optimized (for example, all rows with documents containing the word elections). You can use this mode to optimize index tokens that are frequently searched, without spending time on optimizing tokens that are rarely referenced. An optimized token can improve query response time (but only for queries on that token).
Token-type optimization is similar to token mode, except that the optimization is performed on field sections or MDATA
sections (for example, sections with an <A>
tag). This is useful in keeping critical field or MDATA
sections optimal.
A common strategy for optimizing indexes is to perform regular token optimizations on frequently referenced terms, and to perform rebuild optimizations less frequently. (Use CTX_REPORT.QUERY_LOG_SUMMARY to find out which queries are made most frequently.) You can perform full, fast, or token-type optimizations instead of token optimizations.
Some users choose to perform frequent time-limited full optimizations along with occasional rebuild optimizations.
Note: Optimizing an index can result in better response time only if you insert, delete, or update documents in your base table after your initial indexing operation. |
Using this procedure to optimize your index is recommended over using the ALTER
INDEX
statement.
Optimization of a large index may take a long time. To monitor the progress of a lengthy optimization, log the optimization with CTX_OUTPUT.START_LOG and check the resultant logfile from time to time.
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);
);
Specify the name of the index. If you do not specify an index name, Oracle Text chooses a single index to optimize.
Specify optimization level as a string. You can specify one of the following methods for optimization:
Value | Description |
---|---|
FAST or CTX_DDL.OPTLEVEL_FAST | This method compacts fragmented rows. However, old data is not removed.
Fast optimization is not supported for |
FULL or CTX_DDL.OPTLEVEL_FULL | In this mode you can optimize the entire index or a portion of the index. This method compacts rows and removes old data (deleted rows). Optimizing in full mode runs even when there are no deleted rows.
Full optimization is not supported for |
REBUILD or CTX_DDL.OPTLEVEL_REBUILD | This optlevel rebuilds the $I table (the inverted list table) to produce more compact token info rows. Like FULL optimize, this mode also deletes information pertaining to deleted rows of the base table.
When using REBUILD, setting |
TOKEN or CTX_DDL.OPTLEVEL_TOKEN | This method lets you specify a specific token to be optimized. Oracle Text does a FULL optimization on the token you specify with token. If no token type is provided, 0 (zero) will be used as the default.
Use this method to optimize those tokens that are searched frequently. Token optimization is not supported for |
TOKEN_TYPE or CTX_DDL.OPTLEVEL_TOKEN_TYPE | This optlevel optimizes on demand all tokens in the index matching the input token type.
When Token_type optimization is not supported for |
Specify maximum optimization time, in minutes, for FULL
optimize.
When you specify the symbol CTX_DDL
.MAXTIME_UNLIMITED
(or pass in NULL), the entire index is optimized. This is the default.
Specify the token to be optimized.
If your index is a local index, you must specify the name of the index partition to synchronize otherwise an error is returned.
If your index is a global, non-partitioned index, specify NULL, which is the default.
Specify the token_type
to be optimized.
Specify the parallel degree as a number for parallel optimization. The actual parallel degree depends on your resources. Note that when using REBUILD
, setting parallel_degree
to a value greater than 1 still results in serial execution.
The following two examples are equivalent ways of optimizing an index using fast optimization:
begin ctx_ddl.optimize_index('myidx','FAST'); end; begin ctx_ddl.optimize_index('myidx',CTX_DDL.OPTLEVEL_FAST); end;
The following example optimizes the index token Oracle:
begin ctx_ddl.optimize_index('myidx','token', TOKEN=>'Oracle'); end;
To optimize all tokens of field section MYSEC
in index MYINDEX
:
begin ctx_ddl.optimize_index('myindex', ctx_ddl.optlevel_token_type, token_type=> ctx_report.token_type('myindex','field mysec text'));end;
You can run CTX_DDL.SYNC
and CTX_DDL.OPTIMIZE
at the same time. You can also run CTX_DDL.SYNC
and CTX_DDL.OPTIMIZE
with parallelism at the same time. However, you should not:
run CTX_DDL.SYNC
with parallelism at the same time as CTX_DDL.OPTIMIZE
run CTX_DDL.SYNC
with parallelism at the same time as CTX_DDL.OPTIMIZE
with parallelism.
If you should run one of these combinations, no error is generated; however, one operation will wait until the other is done.
See also CTX_DDL.SYNC_INDEX and ALTER INDEX in Chapter 1, "Oracle Text SQL Statements and Operators".
Removes the index with the specified column list from a CTXCAT
index set preference.
Note: This procedure does not remove aCTXCAT sub-index from the existing index. To do so, you must drop your index and re-index with the modified index set preference. |
CTX_DDL.REMOVE_INDEX(
set_name in varchar2, column_list in varchar2 language in varchar2 default NULL );
Specify the name of the index set
Specify the name of the column list to remove.
Use this procedure to remove metadata values, which are associated with an MDATA
section, from a document. Only the owner of the index is allowed to call ADD_MDATA and REMOVE_MDATA
.
CTX_DDL.REMOVE_MDATA( idx_name IN VARCHAR2, section_name IN VARCHAR2, values SYS.ODCIVARCHAR2LIST, rowids SYS.ODCIRIDLIST, [part_name] IN VARCHAR2] );
Name of the text index that contains the named rowids.
Name of the MDATA
section.
List of metadata values. If a metadata value contains a comma, the comma must be escaped with a backslash.
rowids from which to remove the metadata values.
Name of the index partition, if any. Must be provided for local partitioned indexes and must be NULL for global, non-partitioned indexes.
This example removes the MDATA
value blue from the MDATA
section BGCOLOR
.
ctx_ddl.remove_mdata('idx_docs', 'bgcolor', 'blue', 'rows');
See also "ADD_MDATA"; "ADD_MDATA_SECTION"; "MDATA"; as well as the Section Searching chapter of the Oracle Text Application Developer's Guide.
The REMOVE_SECTION
procedure removes the specified section from the specified section group. You can specify the section by name or by id. You can view section id with the CTX_USER_SECTIONS
view.
Use the following syntax to remove a section by section name:
CTX_DDL.REMOVE_SECTION( group_name in varchar2, section_name in varchar2 );
Specify the name of the section group from which to delete section_name.
Specify the name of the section to delete from group_name.
Use the following syntax to remove a section by section id:
CTX_DDL.REMOVE_SECTION( group_name in varchar2, section_id in number );
Specify the name of the section group from which to delete section_id.
Specify the section id of the section to delete from group_name.
The following code drops a section called Title
from the htmgroup
:
begin ctx_ddl.remove_section('htmgroup', 'Title'); end;
Removes a stopclass from a stoplist.
CTX_DDL.REMOVE_STOPCLASS( stoplist_name in varchar2, stopclass in varchar2 );
Specify the name of the stoplist.
Specify the name of the stopclass to be removed.
The following code removes the stopclass NUMBERS
from the stoplist mystop
.
begin ctx_ddl.remove_stopclass('mystop', 'NUMBERS'); end;
Removes a stoptheme from a stoplist.
CTX_DDL.REMOVE_STOPTHEME( stoplist_name in varchar2, stoptheme in varchar2 );
Specify the name of the stoplist.
Specify the stoptheme to be removed from stoplist_name.
The following code removes the stoptheme banking from the stoplist mystop
:
begin ctx_ddl.remove_stoptheme('mystop', 'banking'); end;
Removes a stopword from a stoplist. To have the removal of a stopword be reflected in the index, you must rebuild your index.
CTX_DDL.REMOVE_STOPWORD(
stoplist_name in varchar2, stopword in varchar2, language in varchar2 default NULL
);
Specify the name of the stoplist.
Specify the stopword to be removed from stoplist_name.
Specify the language of stopword
to remove when the stoplist you specify with stoplist_name
is of type MULTI_STOPLIST
. You must specify the Globalization Support name or abbreviation of an Oracle Text-supported language. You can also remove ALL stopwords.
The following code removes a stopword because from the stoplist mystop
:
begin
ctx_ddl.remove_stopword('mystop','because');
end;
Use this procedure to replace metadata in local domain indexes at the global (index) level.
Note: TheALTER INDEX PARAMETERS command performs the same function as this procedure and can replace more than just metadata. For that reason, using ALTER INDEX PARAMETERS is the preferred method of replacing metadata at the global (index) level and should be used in place of this procedure when possible. For more information, see "ALTER INDEX PARAMETERS Syntax".
|
CTX_DDL.REPLACE_INDEX_METADATA(idx_name IN VARCHAR2, parameter_string IN VARCHAR2);
Specify the name of the index whose metadata you want to replace.
Specify the parameter string to be passed to ALTER INDEX
. This must begin with 'REPLACE METADATA
'.
ALTER INDEX REBUILD PARAMETERS ('REPLACE METADATA')
does not work for a local partitioned index at the index (global) level; you cannot, for example, use that ALTER INDEX
syntax to change a global preference, such as filter or lexer type, without rebuilding the index. Therefore, CTX_DDL.REPLACE_INDEX_METADATA
is provided as a method of overcoming this limitation of ALTER INDEX
.
Though it is meant as a way to replace metadata for a local partitioned index, CTX_DDL.REPLACE_INDEX_METADATA
can be used on a global, non-partitioned index, as well.
REPLACE_INDEX_METADATA
cannot be used to change the sync type at the partition level; that is, parameter_string cannot be 'REPLACE METADATA SYNC
'. For that purpose, use ALTER INDEX REBUILD PARTITION
to change the sync type at the partition level.
See also "ALTER INDEX PARAMETERS Syntax" and "ALTER INDEX REBUILD Syntax".
Sets a preference attribute. You use this procedure after you have created a preference with CTX_DDL.CREATE_PREFERENCE.
CTX_DDL.SET_ATTRIBUTE(preference_name IN VARCHAR2, attribute_name IN VARCHAR2, attribute_value IN VARCHAR2);
Specify the name of the preference.
Specify the name of the attribute.
Specify the attribute value. You can specify boolean values as TRUE
or FALSE
, T
or F
, YES
or NO
, Y
or N
, ON
or OFF
, or 1
or 0
.
Specifying File Data Storage
The following example creates a data storage preference called filepref
that tells the system that the files to be indexed are stored in the operating system. The example then uses CTX_DDL.SET_ATTRIBUTE to set the PATH
attribute to the directory /docs
.
begin ctx_ddl.create_preference('filepref', 'FILE_DATASTORE'); ctx_ddl.set_attribute('filepref', 'PATH', '/docs'); end;
See Also: For more information about data storage, see "Datastore Types" in Chapter 2, " Oracle Text Indexing Elements".For more examples of using |
Synchronizes the index to process inserts, updates, and deletes to the base table.
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);
Specify the name of the index.
Specify the runtime memory to use for synchronization. This value overrides the DEFAULT_INDEX_MEMORY
system parameter.
The memory parameter specifies the amount of memory Oracle Text uses for the synchronization operation before flushing the index to disk. Specifying a large amount of memory:
improves indexing performance because there is less I/O
improves query performance and maintenance because there is less fragmentation
Specifying smaller amounts of memory increases disk I/O and index fragmentation, but might be useful when runtime memory is scarce.
If your index is a local index, you must specify the name of the index partition to synchronize otherwise an error is returned.
If your index is a global, non-partitioned index, specify NULL, which is the default.
Specify the degree to run parallel synchronize. A number greater than 1 turns on parallel synchronize. The actual degree of parallelism might be smaller depending on your resources.
The following example synchronizes the index myindex
with 2 megabytes of memory:
begin
ctx_ddl.sync_index('myindex', '2M');
end;
The following example synchronizes the part1
index partition with 2 megabytes of memory:
begin
ctx_ddl.sync_index('myindex', '2M', 'part1');
end;
You can run CTX_DDL.SYNC
and CTX_DDL.OPTIMIZE
at the same time. You can also run CTX_DDL.SYNC
and CTX_DDL.OPTIMIZE
with parallelism at the same time. However, you should not run CTX_DDL.SYNC
with parallelism at the same time as CTX_DDL.OPTIMIZE
, nor CTX_DDL.SYNC
with parallelism at the same time as CTX_DDL.OPTIMIZE
with parallelism. If you should run one of these combinations, no error is generated; however, one operation will wait until the other is done.
ALTER INDEX in Chapter 1, "Oracle Text SQL Statements and Operators"
Removes a set attribute from a preference.
CTX_DDL.UNSET_ATTRIBUTE(preference_name varchar2, attribute_name varchar2);
Specify the name of the preference.
Specify the name of the attribute.
Enabling/Disabling Alternate Spelling
The following example shows how you can enable alternate spelling for German and disable alternate spelling with CTX_DDL.UNSET_ATTRIBUTE
:
begin ctx_ddl.create_preference('GERMAN_LEX', 'BASIC_LEXER'); ctx_ddl.set_attribute('GERMAN_LEX', 'ALTERNATE_SPELLING', 'GERMAN'); end;
To disable alternate spelling, use the CTX_DDL.UNSET_ATTRIBUTE
procedure as follows:
begin ctx_ddl.unset_attribute('GERMAN_LEX', 'ALTERNATE_SPELLING'); end;
Updates a policy created with CREATE_POLICY. Replaces the preferences of the policy. Null arguments are not replaced.
CTX_DDL.UPDATE_POLICY( policy_name IN VARCHAR2 DEFAULT NULL, filter IN VARCHAR2 DEFAULT NULL, section_group IN VARCHAR2 DEFAULT NULL, lexer IN VARCHAR2 DEFAULT NULL, stoplist IN VARCHAR2 DEFAULT NULL, wordlist IN VARCHAR2 DEFAULT NULL);
Specify the name of the policy to update.
Specify the filter preference to use.
Specify the section group to use.
Specify the lexer preference to use.
specify the stoplist to use.
Specify the wordlist to use.