Querying Stopwords

Stopwords are words for which Oracle Text does not create an index entry. They are usually common words in your language that are unlikely to be searched.

Oracle Text includes a default list of stopwords for your language. This list is called a stoplist. For example, in English, the words this and that are defined as stopwords in the default stoplist. You can modify the default stoplist or create new stoplists with the CTX_DDL package. You can also add stopwords after indexing with the ALTER INDEX statement.

You cannot query on a stopword itself or on a phrase composed of only stopwords. For example, a query on the word this returns no hits when this is defined as a stopword.

Because the Oracle Text index records the position of stopwords even though it does not create an index entry for them, you can query phrases that contain stopwords as well as indexable words, such as this boy talks to that girl.

When you include a stopword within your query phrase, the stopword matches any word. For example, the following query assumes that was is a stopword. It matches phrases such as Jack is big and Jack grew big. It also matches grew, even though it is not a stopword.

'Jack was big'

Starting with Oracle Database 12c Release 2 (12.2), stopwords and unary operators on stopwords are ignored at the initial stages of a query result in different query results than earlier releases. For example, the following query does not return documents because the is a stopword and the $ operator and the stopword are ignored during query processing:

SQL> select count(1) from tabx where contains(text,'$the')>0;
 .
  COUNT(1)
 ----------
        0

The next query returns documents containing first because the the stopword and the $ operator are ignored.

SQL> select count(1) from tabx where contains(text,'first and $the')>0;
 .
  COUNT(1)
 ----------
        2