How to search date ranges

From: Michael Gill <>
Date: Sun, 27 Jan 2002 17:52:30 GMT
Message-ID: <Xns91A3788CC9DC7mydba_at_142.165.21.10>

Ladies and Gentlemen (and geeks, too :-)

I'm having to execute a function in Oracle 8.1.5 that walks a cursor to see if any items conflict with the current schedule. This list will be about 100 rows long and 2 person_id's are interrogated. The process is thus:

Given a row of a PROPOSED SCHEDULE:
StartTime StopTime person_1 person_2 activity

find all events scheduled for each person (using SCHEDULE.person_id), in the EVENT table by joining on the event_id, compare the start and stop times of each event for this person to the PROPOSED SCHEDULE times. Count the occurrance of any overlap.
I visualize it this way. The first line is the PROPOSED SCHEDULE row. The following lines are the possible ways a scheduled event might conflict.

           start           stop




  Event_id (FK)   

  A person may have 800 SCHEDULED records, there are many thousands in EVENT.        SELECT Count(s.Person_ID)
   FROM SCHEDULED s, Event e
   WHERE ((e.Start_Time > (TO_DATE('14-nov_01')) AND (e.Start_Time < TO_DATE('15-nov_01'))) OR

(e.Stop_Time > (TO_DATE('14-nov_01')) AND (e.Start_Time <
TO_DATE('15-nov_01'))) OR

((e.Start_Time <= TO_DATE('14-nov_01')) AND (e.Stop_Time >=
TO_DATE('15-nov_01')))) AND

(e.Event_ID = s.Event_ID) AND

                    (s.Person_ID = 9957)                     

SELECT STATEMENT Optimizer=CHOOSE (Cost=96 Card=1 Bytes=69)   SORT (AGGREGATE)
    HASH JOIN (Cost=96 Card=424 Bytes=29256)       TABLE ACCESS (FULL) OF PERSON_UNAVAILABILITY (Cost=22 Card=424 Bytes=8056)

      TABLE ACCESS (FULL) OF EVENT (Cost=50 Card=17421 Bytes=871050)

Yes, FULL. Index hints only make it worse!? There are indexes on EVENT (each of the 3 columns shown, as well as a composite (to try out) on start_time + stop_time). And on SCHEDULED (event_id and person_id)

I might have to hit this function 100,000 times at a single user keyclick !!!!!! I have to do better than 96 OR perhaps there's a better way to approach the problem ????

Please help!!

TIA, Michael Received on Sun Jan 27 2002 - 18:52:30 CET

Original text of this message