All Forums Database
shrikrishna 3 posts Joined 12/05
10 Mar 2006
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

10 Mar 2006

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

williamdieter 15 posts Joined 03/06
21 Mar 2006

If you are trying to get the month and year for a date, you might want to try using something elseselect*frommytablewhereextract(year from columnname) = 2006andextract(month from columnname) = 3;or select*frommytablewherecolumnname between '2006-03-01' and '2006-03-31';orselect*frommytablewherecolumnname 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.

You must sign in to leave a comment.