Re: Oracle 9: Date Compare Performance
Date: Thu, 29 Jan 2009 07:35:19 -0800 (PST)
On Jan 29, 9:36 am, "MrBana..._at_googlemail.com" <MrBana..._at_googlemail.com> wrote:
> 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
> 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
> 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
Have you looked at the EXPLAIN PLAN for each. They are very different queries. One thing I would take a WAG at is the DISTINCT. IOW, with the >= version, a large number of rows are returned which are then sorted to perform the DISTINCT. While the = version has a much smaller set to sort through.
> Does anybody have a workaround?
Add the conditions to avoid the DISTINCT. (again a WAG)
> Thanks a lot in advance...
Welcome. Let us know what you find out.
Ed Received on Thu Jan 29 2009 - 09:35:19 CST