 | |
Oracle 9: Date Compare Performance
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Oracle 9: Date Compare Performance -
01-29-2009
, 08:36 AM
#2
| |||
| |||
|
|
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 There is a big difference between = and >= and <=. You are comparing a lot |
Jim
#3
| |||
| |||
|
|
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? |
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.
Quote:
|
Does anybody have a workaround? |
Quote:
|
Thanks a lot in advance... Volker |
Ed
#4
| |||
| |||
|
|
On Jan 29, 9:36*am, "MrBana... (AT) googlemail (DOT) com" MrBana... (AT) googlemail (DOT) 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 - |
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 --
#5
| |||
| |||
|
|
On Jan 29, 10:35*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Jan 29, 9:36*am, "MrBana... (AT) googlemail (DOT) com" MrBana... (AT) googlemail (DOT) 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 -- |
#6
| |||
| |||
|
|
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 This query seems to hide that SCHEDULE is the same table as |
Furthermore, your only checking for complete overlaps (includes), not
partial overlaps. Correct?
Shakespeare
#7
| |||
| |||
|
|
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 (DOT) com> wrote: On Jan 29, 10:35*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Jan 29, 9:36*am, "MrBana... (AT) googlemail (DOT) com" MrBana... (AT) googlemail (DOT) 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 - |
* 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
#8
| |||
| |||
|
|
This query seems to hide that SCHEDULE is the same table as ova_schedule. Am I correct in this? That was my mistake... yes they are the same. |
Quote:
|
Furthermore, your only checking for complete overlaps (includes), not partial overlaps. Correct? That s correct. |
--
Volker
#9
| |||
| |||
|
|
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 (DOT) com> wrote: On Jan 29, 10:35*am, Ed Prochak <edproc... (AT) gmail (DOT) com> wrote: On Jan 29, 9:36*am, "MrBana... (AT) googlemail (DOT) com" MrBana... (AT) googlemail (DOT) 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 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 --
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.