Re: Oracle 9: Date Compare Performance

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 30 Jan 2009 12:58:21 +0100
Message-ID: <4982eb5f$0$190$e4fe514c_at_news.xs4all.nl>

MrBanabas_at_googlemail.com schreef:
> 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
>
>
>
>
>
>

This query seems to hide that SCHEDULE is the same table as ova_schedule. Am I correct in this?
Furthermore, your only checking for complete overlaps (includes), not partial overlaps. Correct?

Shakespeare Received on Fri Jan 30 2009 - 05:58:21 CST