Re: Oracle 9: Date Compare Performance

<MrBanabas_at_googlemail.com> wrote in message news:3e65dd6a-72d5-4514-8b1a-46238e470bff_at_n33g2000pri.googlegroups.com...
> Hi,
>
> I ve got a table named schedule which includes epg information round
> about 60.000 records. I would like to check if there are logical
> conflicts.
> One kind of conflict is that a program start stop time frame includes
> other programs...
>
> Therefore I ve got this select statement:
> SELECT Distinct schd.chan_recid, schd.SCHD_START_DATE,
> schd.SCHD_END_DATE, schd.schd_import_refnum,
> include_schd.schd_import_refnum as
> overlapping_schd_import_refnum,
> include_schd.schd_start_date as overlapping_start,
> include_schd.schd_end_date as overlapping_end
> FROM SCHEDULE schd
> LEFT JOIN ova_schedule include_schd
> on (schd.CHAN_RECID = include_schd.CHAN_RECID and
> include_schd.schd_recid != schd.schd_recid and
> include_schd.schd_start_date >= schd.schd_start_date and
> include_schd.schd_end_date <= schd.schd_end_date)
> WHERE (include_schd.schd_import_refnum is not null)
>
> To my suprise it takes over a minute to complete!!!
>
> If I change the date compares from >= and <= to just = :
> include_schd.schd_start_date = schd.schd_start_date and
> include_schd.schd_end_date = schd.schd_end_date
>
> It just takes round about 10 seconds.
>
> Does anybody know why the date comparison operators > and < are so
> slow?
>
> Does anybody have a workaround?
>
> Thanks a lot in advance...
> Volker
>
>
>
>
>
>

There is a big difference between = and >= and <=. You are comparing a lot more records with the inequality operators. Jim Received on Thu Jan 29 2009 - 09:14:07 CST