Spool Space Error

Database

Spool Space Error

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

2 REPLIES
Enthusiast

Re: Spool Space Error

could you send the query that you are using.
There could be a lot of reasons why you get spool error.
and check the explain too to see how the rows are getting distributed

Enthusiast

Re: Spool Space Error

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
;

or

select
*
from
mytable
where
columnname between '2006-03-01' and '2006-03-31'
;

or

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.