Re: Oracle 9: Date Compare Performance

From: <MrBanabas_at_googlemail.com>
Date: Fri, 30 Jan 2009 03:25:52 -0800 (PST)
Message-ID: <f805b7a7-aa3e-46e8-a791-1207180f4e9c_at_r15g2000prh.googlegroups.com>

Thanks a lot for all your reponses.
However, unfortunelty, the executions plans for both queries are the same.
Distinct or not does nt make any difference to the overall performance in my special case and optimizer statistics are current.

Therefore it seems to me that it s just the comparision operator change which slows down my query.

I ve defined several indeces for the relevant columns, but oracle is not using them for both queries. I ve tried already to force the usage of indexes, but that s not helping also... :-(

Execution Plan:

Select statement()               null
  sort (unique)                      null
    hash join ()                       null
    Table Access (FULL)       Ova_schedule
    table access (FULL)        Ova_schedule

Any other ideas???

--
Volker


On 29 Jan., 19:18, Mark D Powell <Mark.Pow..._at_eds.com> wrote:


> On Jan 29, 10:35 am, Ed Prochak <edproc..._at_gmail.com> wrote:

>

>

>

> > On Jan 29, 9:36 am, "MrBana..._at_googlemail.com"

>

> > <MrBana..._at_googlemail.com> 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 --

Received on Fri Jan 30 2009 - 05:25:52 CST