My SQL SELECT query operating on a nonSQL defined database refuses to use an index.

Document ID : KB000051408
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

I have a nonSQL defined database record with an index on it, and an SQL schema defined for the nonSQL schema. When I run a simple SQL SELECT against the record, with the exact symbolic key field of the index in the WHERE clause, the query does an area sweep - it refuses to use the index. Why is this?

Solution:

The most common cause of this problem is that the nonSQL index is defined as something other than MANDATORY AUTOMATIC. Only MANDATORY AUTOMATIC indexes can be considered for use with SQL because for any other type of index, there is no guarantee that all occurrences of that record type are included in the index.