im writing a sql query with Left join :
from tbl_cust_info a
left join tbl_ordr_date b on a.customer=b.customer
( b.ordr_date between a.starting_date and a.ending_date)
it is showing me no more spool space , can i mention the BETWEEN condition in the Left join ?
if i use the 'Where clause' also the query is not executing . suggest me
Queries aborting due to insufficient spool is because the quantity of data that is brought in spool for joining between the two tables is more than the allocated spool space for the user. Mostly this is because of improper primary index selection, because the rows needs to be on the same AMP to be joined together regardless of the type of join inner/outer.
BETWEEN clause is more resource intensive and normally ends up in full table scan.
You need to assign more spool space to the user.
If you use AND operator in OUTER JOIN it will take more SPOOL, try WHERE clause but it will filter the result based on the inner table.