Overriding the XDB Optimizer

Usually, it is faster to execute a query using one or more indexes than to examine every record in the tables involved in the query. XDB Server's query optimization algorithms automatically examine the indexes available and pick which indexes, if any, to use for a particular query. The user normally does not need to specify which indexes to use.

You can force the use of an index for a particular query or force the system to not use an index. To force the use of an index, enter an asterisk (*) in parentheses after the name of the indexed column when you construct the query. To force the system to ignore the index, enter a hyphen (-) in parentheses after the name of the indexed column when you construct the query.

For example, assuming that an index exists on C_NO:

This query uses the C_NO index.

SELECT c_no, o_no, o_date
  FROM orders
  WHERE c_no(*)<20

This query does not use the index.

SELECT c_no, o_no, o_date
  FROM orders
  WHERE c_no(-)<20

The (*) and (-) options are valid only for column names that appear in a WHERE clause. You cannot use the (*) and (-) options in SELECT, GROUP BY, HAVING or ORDER BY clauses.