All Forums Database
sunny.j 90 posts Joined 10/10
16 Oct 2012
SQL Help : Left join

Hi
 
im writing a sql  query with Left join  :
 
sel
a.*
,b.ordr_date
from   tbl_cust_info  a
left join  tbl_ordr_date  b on  a.customer=b.customer 
and
( 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
 

sunny.j 90 posts Joined 10/10
16 Oct 2012

ANY  suggestions about the same

Qaisar Kiani 337 posts Joined 11/05
16 Oct 2012

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.

Mathuram 21 posts Joined 09/12
18 Oct 2012

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.

You must sign in to leave a comment.