Question: Why didn't this query fully optimize?

Answer: I ran into a situation where writing the WHERE clause of a SELECT in a fashion that made it more readable to me, also made it so that it couldn't be fully optimized.

select  *;
   from history ;
   into cursor xtemp ;
   where ( ( ltStart <= tCreated ) and ( tCreated < ltStop ) ) ;
   order by iMachineID, tCreated

The cursor is getting data beginning at some time (ltStart), upto a end time (ltStop). The cursor does not include the end point. While testing the cursor was taking 20-30 seconds to retrieve the data from a one million record table. I used SYS(3054,1) and SYS(3054,11) to turn on the diagnostics and found the query was only partially optimized.

While debuging it I took the WHERE clause as two separate pieces to see which part could not be optimized. The ( ltStart <= tCreated ) was at fault, the SQL engine did not optimize that expression.

It took rewriting the query like this:

select  *;
   from history ;
   into cursor xtemp ;
   where ( ( tCreated >= ltStart ) and ( tCreated < ltStop ) ) ;
   order by iMachineID, tCreated

to get it to optimize. The only difference is that the field is on the left side of the operators in both parts.

So in order to get the SQL optimizer to work most efficiently you must put the field on the left side of the the operator.