out of spool space

Database

out of spool space

Hi All,

 

I have millions appx records in the tables and I am trying to run the query. I m sure the below is not tunned. Is there any way we could make it better? I tried to create seperate volatile tables and do the calculation for contact number but still showing " out of spool" ..and also we are searching char which is not good for query but I have to get the outpu by using chars as filter as below.

 

select top 1
sta.PATY_i
,GRD1.DEPT_LEAF_NODE_C as BUSINESS_UNIT
, FLA.ANZI_LEAF_NODE_M AS ENTITY_TYPES
,pat.SURB_X AS CITY
,pat.STAT_C AS STATE
,pat.ISO_CNTY_C AS COUNTRY
, CASE WHEN STA.PATY_STUS_CATG_C ='AMLV' AND STA.paty_stus_c in ('NVRF' or 'UNKN' ) THEN 'N'
WHEN STA.PATY_STUS_CATG_C ='AMLV' AND STA.paty_stus_c ='VERF' THEN 'Y' ELSE STA.PATY_STUS_CATG_C
END AML_FLAG
,STA.EFFT_D AS AML_CREATED_DATE
,LIF.EFFT_D AS CUST_CRE_DATE
,oreplace ( ( oreplace ( tele_numb,'(','')) ,')','') as Contact_number
,case when length( oreplace ( ( oreplace ( tele_numb,'(','')) ,')',''))='10' then 'Valid_phone' else 'Invalid_phone'end as Contact_Number_Validation
,emp.PATY_EMPL_ROLE_C
,empd.EMPL_M AS EMPLOYEE_NAME
from PVDATA.DERV_PRTF_PATY_REL_CURR t1
INNER join
PVDATA.INT_GRUP_DEPT_CURR grp1
on t1.INT_GRUP_I = grp1.INT_GRUP_I
inner join
pvdata.GRD_DEPT_FLAT_CURR grd1
on grp1.DEPT_I = grd1.DEPT_LEAF_NODE_C
inner join pvdata.PATY_INDS_GRUP_CURR cuu
on cuu.paty_i=t1.paty_I
inner join pvdata.PATY_ADRS_CURR pva
on pva.PATY_I=cuu.paty_i
inner join pvdata.PHYS_ADRS_CURR pat
on pat.ADRS_I=pva.ADRS_I
inner join pvdata.PATY_STUS_curr sta
on sta.PATY_I=pva.PATY_I
inner join
pvdata.paty_inds_grup_curr gru
on gru.PATY_I=sta.paty_i
inner join pvdata.GRD_ANZI_FLAT_CURR fla
on gru.ANZI_C=fla.anzi_leaf_node_c
inner join pvdata.PATY_EMPL_REL_CURR emp
on emp.PATY_I=sta.paty_i
inner join PVCBODS.BUSN_PTNR busn
on busn.PATY_I=sta.paty_i
inner join
PVCBODS.BUSN_PTNR_TELE_CTCT tel
on tel.sap_busn_pTNR_numb=busn. busn_PTNR_numb
inner join pvdata.PATY_INT_GRUP_curr grp
on grp.PATY_I=sta.PATY_I
inner join PVDATA.INT_GRUP_CURR grcu
on grp.INT_GRUP_I=grcu.INT_GRUP_I
inner join pvdata.INT_GRUP_EMPL_curr cuur
on cuur.INT_GRUP_I=grcu.INT_GRUP_I
inner join pvssrd.empl_dimn empd
on empd.empl_i=cuur.empl_i
INNER JOIN pvdatA.PATY_LIFE_CYCL_CURR LIF
ON LIF.PATY_I=STA.PATY_I

where sta.PATY_STUS_CATG_C = 'AMLV' and sta.PATY_STUS_C = 'VERF'

 

3 REPLIES
N/A

Re: out of spool space

It's not the contact number calculation which results in no more spool, although it can be simplified to:

OTranslate( tele_numb,'()','') AS Contact_number
,CASE WHEN Char_Length(Contact_number)=10 THEN 'Valid_phone' ELSE 'Invalid_phone'end AS Contact_Number_Validation

You got 16 joins, without knowledge about DDL and Explain (and/or DBQLSteps) there's no way to help you.

 

Do you really need a TOP 1?

I assume this was just for testing, in fact this will not speed up your query because all joins are done first and then the TOP.

Re: out of spool space

A couple of suggestions if you haven't already done this. Either collect stats on all of the columns referenced in the joins and  the columns referenced in the WHERE clause or do a "diagnostic helpstats on for session;" followed by an "explain" of the SQL and then go to the bottom of the explain and execute the recommended stats that are listed there.

 

These are both "fire hose" approaches that may result in you collecting more statistics than you really need. The better approach is to analyze the explain, but if you're in a hurry, the above suggestion may solve your problem.

Re: out of spool space

Hi Dieter,

 

thank you very much for the help.

 

Issue is resolved as multiple queries were running in parallel hence the space issue.

 

Regards,

Hari