Re: Oracle 9: Date Compare Performance
From: ddf <oratune_at_msn.com>
Date: Fri, 30 Jan 2009 05:16:38 -0800 (PST)
Message-ID: <60871a98-bcd3-44b2-86ad-15076f59b3bd_at_w24g2000prd.googlegroups.com>
On Jan 30, 5:25 am, "MrBana..._at_googlemail.com" <MrBana..._at_googlemail.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 30 Jan 2009 05:16:38 -0800 (PST)
Message-ID: <60871a98-bcd3-44b2-86ad-15076f59b3bd_at_w24g2000prd.googlegroups.com>
On Jan 30, 5:25 am, "MrBana..._at_googlemail.com" <MrBana..._at_googlemail.com> wrote:
> 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 --- Hide quoted text -
>
> - Show quoted text -
Yes, provide the following information:
- Oracle version, all four to five numbers
- DDL to create the problem tables and indexes
- Sample data (if possible)
We're guessing, at best, given the information (or lack thereof) you've supplied. Having the DDL and sample data, along with the Oracle version, can go a long way in helping us help you.
David Fitzjarrell Received on Fri Jan 30 2009 - 07:16:38 CST