Oracle 9: Date Compare Performance

From: <MrBanabas_at_googlemail.com>
Date: Thu, 29 Jan 2009 06:36:29 -0800 (PST)
Message-ID: <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 Received on Thu Jan 29 2009 - 08:36:29 CST