How to configure stopwords

In an Oracle full-text index, stop words represent common words that are not indexed. A list of such words is called a stoplist. Oracle has default stoplists for most languages, but it is possible to modify the default stoplist or to create new stoplists.

Modifying the default stoplist

The default stoplist is CTXSYS.DEFAULT_STOPLIST, and it used when the index is created without specifying a stoplist. Stopwords can be added or removed from this stoplist as in the following script:

begin
	ctx_ddl.add_stopword('CTXSYS.DEFAULT_STOPLIST', 'example');
	ctx_ddl.remove_stopword('CTXSYS.DEFAULT_STOPLIST', 'example');
end; 

When changing the default stoplist, the following points must be taken into account:

• the default stoplist is owned by the sys user. In order to change the stoplist, the script must be executed as sys.
• after changing the default stoplist (either adding or removing words), the full-text index needs to be re-created
• simply rebuilding the index does not have any effect

Creating a new stoplist

A new stoplist can be created and stopwords can be added or removed as in the following script:

begin
	ctx_ddl.create_stoplist('example_stoplist', 'BASIC_STOPLIST');
 
	-- adding a stopword
	ctx_ddl.add_stopword('example_stoplist', 'the');
	
	-- or removing a stopword
	ctx_ddl.remove_stopword('example_stoplist', 'other');
end;

The stoplist can be configured on the index at creation time as follows:

drop index aq_fulltext_index;
create index aq_fulltext_index on aq_fulltext(text)
  indextype is ctxsys.context
  parameters ('datastore aq_fulltext_uds stoplist example_stoplist sync(every "sysdate+1/24")');

The stoplist can also be changed without re-creating the index using the 'alter index rebuild' statement:

alter index aq_fulltext_index rebuild parameters ('replace stoplist example_stoplist');

The above statement must be executed each time the stoplist is modified in order for the changes to the stoplist take effect.

Dynamically adding stopwords

It is possible to add stopwords to the stoplist of the full-text index without having to rebuild the index using the 'alter index parameters' statement:

alter index aq_fulltext_index parameters ('add stopword example'); -- stopword = example

It is not possible to dynamically remove a stopword.

When adding stopwords dynamically, the following points must be taken into consideration:

• the index doesn't need to be rebuilt, the stopword is immediately removed from the index
• if the index uses the default stoplist, a stopword can be dynamically added by a user other than sys
• dynamically adding a stopword to the index does not change the stoplist used by the index. This means that the following script may have undesired effects:

begin
	-- create a custom stoplist
	ctx_ddl.create_stoplist('example_stoplist, 'BASIC_STOPLIST');
 
	-- add the word 'the' to the stoplist
	ctx_ddl.add_stopword('example_stoplist', 'the');
end;
 
-- create the full-text index with the custom stoplist
create index aq_fulltext_index on aq_fulltext(text) 
	indextype is ctxsys.context 
	parameters ('datastore aq_fulltext_uds stoplist example_stoplist sync(on commit)');
 
-- dynamically add the stopword 'other' to the index
alter index aq_fulltext_index parameters ('add stopword other');
 
-- rebuild the index and replace the stoplist (perhaps new stopwords were added to the stoplist)
-- the stopword 'other' is lost, and again present in the index
alter index aq_fulltext_index rebuild parameters ('replace stoplist example_stoplist');
 

It is recommended that if stopwords are dynamically added to the index, then the same stopwords are also added to the stoplist used by the index. In this way, if the stoplist ever has to be replaced, the dynamically added stopwords are not lost.