Re: Aggregate query problem

On 11 Jul 2002 13:53:42 -0700, barnest_at_san.rr.com (Tom Barnes) wrote:

>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

Please provide an example of the data.
You seem to using a very contorted approach where a simple
select
where <date_column> =
(select min
  from ...
)
should have done the tric.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Jul 11 2002 - 16:47:43 CDT