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?
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.