Using the OR operator in a WHERE clause

To optimize performance, try to use the OR operator to create queries that combine the second parameters together within a single CONTAINS or SCORE function in a WHERE clause, if the first parameter for the CONTAINS or SCORE functions is the same.

Consider the following query, which has two separate CONTAINS functions:

SELECT C2 FROM T1 WHERE CONTAINS(C2, 'fence') = 1 OR CONTAINS(C2, 'horse') = 1;

The first parameter in these two CONTAINS functions is the same, and the second parameter is different.

You can rewrite this query to optimize performance by using the OR operator to combine the two CONTAINS functions into one. The updated query is as follows:

SELECT C2 FROM T1 WHERE CONTAINS(C2, ' (fence) OR (horse) ') = 1;

In some cases the usage of the OR operator in a WHERE clause cannot be avoided. For example, if the query involves the CONTAINS and SCORE functions and several different text search indexes.

Consider that a table is created with two full-text indexes on the columns C2 and C3®, as follows:

CREATE TABLE T1(RID ROWID NOT NULL GENERATED BY DEFAULT, C2 VARCHAR(20),  
C3 VARCHAR(20));

You can use two approaches to improve the performance of the following two queries that use scalar search functions:

Query 1:
SELECT C2, C3 FROM T1 WHERE CONTAINS(C2, 'fence') = 1 OR CONTAINS(C3, 'horse') = 1;

Query 2:
SELECT C2, C3 FROM T1 WHERE CONTAINS(C2, 'fence') = 1 OR C3='horse';

One way to improve the performance of these queries is to rewrite the queries as table functions, as follows:

Query 1:
WITH CONTAINS1 (RID) AS (SELECT RID FROM T1 WHERE CONTAINS (C2, 'fence') = 1),
     CONTAINS2 (RID) AS (SELECT RID FROM T1 WHERE CONTAINS (C3, 'horse') = 1)
SELECT DISTINCT C2, C3 FROM T1 T, CONTAINS1, CONTAINS2 WHERE 
T.RID=CONTAINS1.RID OR T.RID=CONTAINS2.RID;

Query 2:
WITH CONTAINS1 (RID) AS (SELECT RID FROM T1 WHERE CONTAINS (C2, 'fence') = 1)
SELECT DISTINCT C2, C3 FROM T1 T, CONTAINS1 WHERE 
T.RID=CONTAINS1.RID OR C3='horse';

The second approach that you can use to improve performance is to rewrite the queries by using UNION instead of an OR operator. For example:

Query 1:
SELECT C2, C3 FROM T1 WHERE CONTAINS(C2, 'fence') = 1 
UNION
SELECT C2, C3 FROM T1 WHERE CONTAINS(C3, 'horse') = 1;

Query 2:
SELECT C2, C3 FROM T1 WHERE CONTAINS(C2, 'fence') = 1 
UNION
SELECT C2, C3 FROM T1 WHERE C3='horse';