AJV
07-15-03, 12:19
| We face the following problem. We use under Pervasive 2000 (SP 4) the following query (running in PCC): Select * from Objects 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? |