Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to search date ranges
If I understand you correctly, creating an index on (person_id, start_date)
would be quite effective, eh?
Would you explain what you mean by "leading table"? Are you referring to the
order in the FROM clause?
Many thanks
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:hur85us10nvfls12d1vnuki4n2f0tfbkht_at_4ax.com...
> 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 - 21:37:35 CST
![]() |
![]() |