Re: How to search date ranges

On Sun, 27 Jan 2002 17:52:30 GMT, Michael Gill <mydba_at_usa.com> wrote:

>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

The index on event_id, person_id will *not* be used, as person_id is not the leading column. As you have a FTS on the leading table, the optimizer automatically resorts a FTS on all other tables.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sun Jan 27 2002 - 15:25:08 CST