How to search dates effectively

From: Michael Gill News <me_at_work.com>
Date: Sun, 27 Jan 2002 06:14:50 GMT
Message-ID: <uVM48.2548$iOo3.7668213_at_tomcat.sk.sympatico.ca>

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 (on the screen): 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.

           O-----------------O
           start           stop
                 O------O
      O---------O
                           O---------O
    O-------------------------------O

  EVENT

---

  Event_id
  Start_time
  Stop_time

  SCHEDULED

---

  Unavail_id
  Person_id
  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 - 07:14:50 CET