Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Aggregate query problem

Aggregate query problem

From: Tom Barnes <barnest_at_san.rr.com>
Date: 11 Jul 2002 13:53:42 -0700
Message-ID: <ae6b6116.0207111253.4a126dd4@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US