REPOST: How to search date ranges

From: Michael Gill <mydba_at_usa.com>
Date: Sun, 27 Jan 2002 17:52:30 GMT
Message-ID: <0$--$%%%_$%$--$$$$_at_news.noc.cabal.int>

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.

           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

• WAS CANCELLED BY =======: From: Michael Gill <mydba_at_usa.com> Control: cancel <Xns91A3788CC9DC7mydba_at_142.165.21.10> Subject: cmsg cancel <Xns91A3788CC9DC7mydba_at_142.165.21.10> Date: Mon, 28 Jan 2002 01:14:46 GMT Message-ID: <cancel.Xns91A3788CC9DC7mydba_at_142.165.21.10> X-No-Archive: yes Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88 Lines: 1 Path: news.uni-stuttgart.de!news.ruhr-uni-bochum.de!news-koe1.dfn.de!news-was.dfn.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.stealth.net!msrtrans1!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller Xref: news.uni-stuttgart.de control:40720033

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Sun Jan 27 2002 - 18:52:30 CET