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
,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
,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
,empd.EMPL_M AS EMPLOYEE_NAME
from PVDATA.DERV_PRTF_PATY_REL_CURR t1
on t1.INT_GRUP_I = grp1.INT_GRUP_I
on grp1.DEPT_I = grd1.DEPT_LEAF_NODE_C
inner join pvdata.PATY_INDS_GRUP_CURR cuu
inner join pvdata.PATY_ADRS_CURR pva
inner join pvdata.PHYS_ADRS_CURR pat
inner join pvdata.PATY_STUS_curr sta
inner join pvdata.GRD_ANZI_FLAT_CURR fla
inner join pvdata.PATY_EMPL_REL_CURR emp
inner join PVCBODS.BUSN_PTNR busn
on tel.sap_busn_pTNR_numb=busn. busn_PTNR_numb
inner join pvdata.PATY_INT_GRUP_curr grp
inner join PVDATA.INT_GRUP_CURR grcu
inner join pvdata.INT_GRUP_EMPL_curr cuur
inner join pvssrd.empl_dimn empd
INNER JOIN pvdatA.PATY_LIFE_CYCL_CURR LIF
where sta.PATY_STUS_CATG_C = 'AMLV' and sta.PATY_STUS_C = 'VERF'
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.
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.
thank you very much for the help.
Issue is resolved as multiple queries were running in parallel hence the space issue.