How can I speed up a CONTAINS query on more than one column?

Answer: The fastest type of query is one where there is only a single CONTAINS clause and no other conditions in the WHERE clause.

Consider the following multiple CONTAINS query:

SELECT title, isbn FROM booklist
  WHERE CONTAINS (title, 'horse') > 0
    AND CONTAINS (abstract, 'racing') > 0

You can get the same result with section searching and the WITHIN operator:

SELECT title, isbn FROM booklist
  WHERE CONTAINS (alltext,
    'horse WITHIN title AND racing WITHIN abstract')>0

This query is completed more quickly than the single CONTAINS clause. To use a query like this, you must copy all data into a single text column for indexing, with section tags around each column’s data. You can do that with PL/SQL procedures before indexing, or you can use the USER_DATASTORE datastore during indexing to synthesize structured columns with the text column into one document.