We face the following problem.
We use under Pervasive 2000 (SP 4) the following query (running in PCC):
where OBCreateDate >= '2002-07-15'
and OBCreateDate <= '2003-07-26'
and OBLastElabMType = 8900
and OBSeries >= 1
and OBSeries <= 9
and OBLastElabPlant = 1
and OBLastElabMID in (0, 1)
and OBState = 0
and OBStatus = 1
order by OBCreateDate, OBCreateTime, OBLastElabMType, OBSeries, OBObject, OBSubObject
where the 'order by'=
"OBCreateDate, OBCreateTime, OBLastElabMType, OBSeries, OBObject, OBSubObject" (KEY 3) is an index on table objects.
Using "Query Plan" tool of Pervasive we saw that Query Optimizer uses a different key in order to collect data.
Note that execution of the above sql statement takes over 5 minutes (objects table has more than 450.000 records), while the same statement
without the order by clause takes 1-2 seconds to be executed.
1. Why query optimizer doesn't use KEY 3 as we define in order by clause?
2. How it is possible to force Query Optimizer to use KEY 3 which is in order by clause?