How to search date ranges
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.
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)
(e.Stop_Time > (TO_DATE('14-nov_01')) AND (e.Start_Time <
((e.Start_Time <= TO_DATE('14-nov_01')) AND (e.Stop_Time >=
FROM SCHEDULED s, Event e
WHERE ((e.Start_Time > (TO_DATE('14-nov_01')) AND (e.Start_Time <
TO_DATE('15-nov_01'))) OR
TO_DATE('15-nov_01'))) OR
TO_DATE('15-nov_01')))) 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