Adding stopwords

create table foo(text varchar2(80));
insert into foo values ('the cat sat on the mat');
create index fooindex on foo(text) indextype is ctxsys.context
parameters('stoplist CTXSYS.EMPTY_STOPLIST sync(on commit)');

Check the indexed words:

select distinct token_text from dr$fooindex$i;

Create a new stoplist. Note that LiveSQL doesn't properly clear stoplists when you clear a session, so you may need to drop an existing stoplist first if you've run the tutorial before

exec ctx_ddl.drop_stoplist('new_stoplist')

Now create the new one

exec ctx_ddl.create_stoplist('new_stoplist', 'BASIC_STOPLIST')
exec ctx_ddl.add_stopword('new_stoplist', 'the')
exec ctx_ddl.add_stopword('new_stoplist', 'on')
exec ctx_ddl.add_stopword('new_stoplist', 'a')

Alter the index to use the new stoplist

alter index fooindex rebuild parameters('REPLACE METADATA stoplist new_stoplist');

That won't affect previously-indexed words in the index Add a new doc which uses the word 'a' just defined as a stopword

insert into foo values ('two dogs sat on a cat');
commit;

Now look again at the words indexed - no 'A' in there!

select distinct token_text from dr$fooindex$i;

Check that stopwords are working properly. If they are, then 'the' in a query will match any word:

REM 'cat the' in the query will match 'cat sat' in the text. See the documentation if that doesn't make sense!
select * from foo where contains (text, 'cat the on the mat') > 0;

Next, we might want to clean up the wordlist to remove our stopwords. This isn't completely necessary (and, arguably, is not fully supported) but it will free up space in the index. Be careful doing this if you use the SUBSTRING_INDEX or WILDCARD_INDEX options.

delete from dr$fooindex$i where token_text in ('THE', 'ON', 'A');
select distinct token_text from dr$fooindex$i;

Finally, we'll show a way of adding individual stopwords to an index without replacing the whole stoplist

alter index fooindex rebuild parameters('add stopword ONE');
REM the word ONE is now a stopword, so when we add this string ONE will not be indexed:
insert into foo values ('the cat sat on one mat');
commit;
select distinct token_text from dr$fooindex$i;