is the 'date' column (bad name for a column BTW) actually of type date? If so, your comparsion with strings is all wrong.
where date between '01DEC2012' and '31DEC2012'
986008 wrote:Well, if it is varchar2 then guess where the Days of January 3042 will be sorted between :
I just changed the field names for the forum. The field is not "date".
You where clause basically says to take every rows whose first digit is between 0 and 3..... +(I guess that is quite a lot in you case....)+
[11.2] Scott @ My11g > l 1 with wannabedates(val) as ( 2 select '01DEC2012' from dual union all 3 select '31DEC2012' from dual union all 4 select '14JAN3042' from dual 5 ) 6 select val 7 from wannabedates 8* order by val [11.2] Scott @ My11g > / VAL --------- 01DEC2012 14JAN3042 31DEC2012
986008 wrote:Parallel query does not always help. How long does it take when run in serial? Does an execution plan indicate that the query really is being run in parallel?
This query takes a long time (couple of hours). Can anyone notice a way to improve it?
create TABLE Temp_A
select /*+ parallel(8)*/ a.field1,
from (select field1 from tableA where date between '01DEC2012' and '31DEC2012'
group by field1 ) a
left join tableB b on a.field1 = b.field1;
I actually have another peice that I stripped out to make it faster because tableA has multiple entries for the single entry in tableB.
I was thinking it might speed it up to add the seperate TableA records later since tableA is smaller than tableB (400million).
Any help would be appreciated...
986008 wrote:Always compare dates with dates. Problems that arise if you don't include:
Someone else wrote the query. I usually use to_date but this seemed to work other than speed.
Is it faster to compare using the to_date vs string? Im changing it now and will see.
Im still running the explain plan.I am not the DBA so I dont have full access to all the features.