Aggregate query problem

I have a problem that I have simplified into the following scenario. I have a table Events, that stores events for employees:

CREATE TABLE Events(EventID NUMBER, EmpID NUMBER, DateTime DATE); CREATE INDEX Events_IX1 ON Events(EmpID, DateTime);

I want a fast query that, for every EmpID, returns the closest event to a specified date, constrained by a specified date/time window.

My best attempt so far is a query like this:

SELECT EventID
FROM Events,
( SELECT MIN(ABS(:1-DateTime)) MinAbs, EmpID   FROM Events
  WHERE DateTime BETWEEN :2 AND :3
  GROUP BY EmpID ) Minimums
WHERE ABS(:1-DateTime) = Minimums.MinAbs AND Minimums.EmpID = Events.EmpID;

which gives me the following execution plan:

|Operation                     |  Name     |


--------------------------------------------

|SELECT STATEMENT              |           |
| NESTED LOOPS                 |           |
|  VIEW                        |           |
|   SORT GROUP BY              |           |
|    INDEX FULL SCAN           |EVENTS_IX1 |
|  TABLE ACCESS BY INDEX ROWID |EVENTS     |
|   INDEX RANGE SCAN           |EVENTS_IX1 |


--------------------------------------------

(The FULL SCAN of EVENTS_IX1 is replaced by a RANGE SCAN on my real system). As you can see EVENTS_IX1 is scanned twice. The inline view is picking out the minimums, why can't it also return the corresponding EventIDs so I wouldn't need a separate join (and hence an extra index scan) to get the EventIDs?

Can I use analytical functions for this?

Oracle 8.1.6 Enterprise Edition on Solaris 2.8

Thanks a lot,

Tom Received on Thu Jul 11 2002 - 15:53:42 CDT