Re: Oracle 9: Date Compare Performance

On Jan 29, 10:35 am, Ed Prochak <edproc...@xxxxxxxxx> wrote:
On Jan 29, 9:36 am, "MrBana...@xxxxxxxxxxxxxx"





<MrBana...@xxxxxxxxxxxxxx> wrote:
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?

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...
Volker

Welcome. Let us know what you find out.

 Ed- Hide quoted text -

- Show quoted text -

As stated look at the explain plan. Also realize that the optimizer
pretty much has to assume you will read X percentage of the data for
range scans bounded only on one side. Even a minor change to the SQL
can be a major change to the optimizer (as stated).

Make sure the optimizer statistics are current for all objects
involved in the query.

HTH -- Mark D Powell --
.