{Slide 3} Clauses are evaluated in the order specified. For that reason you should in general put the most restrictive, or selective clauses first, and the most general ones last. For each clause an index is chosen, if possible. If more than one index can be used the indices are scored as to how many clauses they can help with. Each regular index is first examined quickly to estimate the percentage of the index that will be read. If it exceeds btreethreshold then the index is skipped. The default setting of btreethreshold is 50. If you have very large tables you may want to lower the threshold. The reason for skipping the index is that the system is counting on a later clause being able to restrict the result set much further with less work.

Once the index has been examined for matching records, the list of matching records is merged with any prior list. The result of the merge is the set of records that will need to be looked at.for the given conditions. If the number of records is less than maxlinearrows then Texis will stop looking for more indices. The default setting is 1000. The rationale is that it should be faster to look at maxlinearrows records to determine if they match than to look for another index, and process it.

If an index was created on multiple fields then it will be considered when the current part of the where clause being examined matches the first field of the index. If the first field is doing an '=' query then it can use the index very efficiently if the second field is paired up in the where clause. This can be ensured using parentheses. As entries are pulled from the index any other field in the index that is also in the where clause, and has not yet been resolved, will be examined. For example if you had an index created on fields A and B, and in your where clause you are comparing against A and B you could write: AND (A = AND B = ) AND

This is similar to the behavior of BETWEEN. When Texis sees both a greater than and a less than together, it can bracket the records fetched from the index more efficiently. BETWEEN ensures they are seen together. X BETWEEN and is equivalent to (X >= and X <= )