Basic text search queries

You can run many types of basic text searches, such as word, phrase, Boolean, proximity, and fuzzy. You include the bts_contains() search predicate in the FROM clause of your query. Before you can run a search, you must create a bts index on the column you want to search.

Basic text search queries are not case-sensitive.

Searching on multiple columns

To run a basic text search query on multiple columns, you can create a composite bts index on those columns. If you include the query_default_field="*" index parameter, each column is indexed separately and you can run queries like the following query:

SELECT * FROM address WHERE bts_contains(fname, 'john AND city:nipigon');

Alternatively, you can create a different bts index on each column. However, you cannot use the SQL Boolean predicates AND, OR, and NOT between multiple bts_contains() search predicates in the same predicate clause. For example, the expression, bts_contains(fname, 'john') AND bts_contains(lname, 'smith') is not supported. To query on multiple bts indexes, use a UNION operator to join multiple SELECT statements that each include a different column in the bts_contains() search predicate.

Including the INDEX optimizer directive to force index scans

If you receive BTS22 errors from your queries, the optimizer might not be running the bts_contains() search predicate as an index scan. To force the optimizer to run the bts_contains() search predicate as an index scan, include the INDEX optimizer directive in your query. For example, the following query includes the INDEX optimizer directive for the bts_idx index on the address table:

SELECT INDEX(address bts_idx) * FROM address 
        WHERE bts_contains(fname, 'john AND city:nipigon');