Re: Oracle 9: Date Compare Performance
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Sat, 31 Jan 2009 14:46:54 -0800 (PST)
Message-ID: <db7cefcc-f445-44c6-86d1-8a3c87f957ee_at_k1g2000prb.googlegroups.com>
On Jan 30, 6: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: Sat, 31 Jan 2009 14:46:54 -0800 (PST)
Message-ID: <db7cefcc-f445-44c6-86d1-8a3c87f957ee_at_k1g2000prb.googlegroups.com>
On Jan 30, 6: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 -
The explain plan resutls and what Oracle actually does may differ as the optimier now makes some run-time decisions. If the actual SQL statement being ran has bind variables and histograms exist on the table(s) there bind variable peeking can be an issue.
Check v$sql_plan for the actual plan used to be sure.
HTH -- Mark D Powell -- Received on Sat Jan 31 2009 - 16:46:54 CST