Hi, I am trying to retrive data by using date condition as substring(columnname from 1 for 6)='20060301' but its giving spool space error. Is there any other way to do the same? I am using only 39% of spool space. Thanks Shrikrishna
If you are trying to get the month and year for a date, you might want to try using something else
select * from mytable where extract(year from columnname) = 2006 and extract(month from columnname) = 3 ;
select * from mytable where columnname between '2006-03-01' and '2006-03-31' ;
select * from mytable where columnname between (date - extract(day from date) + 1) and (add_months((date - extract(day from date) + 1),1) -1) ;
If your date column is part of a partitioned primary index, then you will not leverage partition elimination unless you hardcode a date or use simple date arithmetic. One you incorporate a function, partition elimination does not occur.