Help on Query Tunning

Database
JGP
Enthusiast

Help on Query Tunning

Help on Tuning

I have the below set of tables created.

CREATE SET TABLE EDMSIT01_T3D_FND.DIM_PARTY ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Party_Id INTEGER NOT NULL,

Eff_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Eff_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

Party_Desc VARCHAR(400) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Host_Party_Id VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Party_Start_Dt DATE FORMAT 'YYYY-MM-DD',

Party_End_Dt DATE FORMAT 'YYYY-MM-DD',

Lifcyc_Type_Cd INTEGER,

Init_Data_Src_Type_Cd INTEGER,

Assc_Hr_Num VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

Jnt_Party_Cnt INTEGER,

Indiv_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Jnt_Party_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Assc_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Extr_Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Intrnl_Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Fin_Inst_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Dept_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Div_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Brnch_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Region_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Io_Oth_Orgn_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Intrnl_Orgn_Num VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Orgn_Ctr_Cd INTEGER,

Orgn_Num VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Intrnl_Orgn_Bank_Num VARCHAR(32) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Hr_Intrnl_Orgn_Ind CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Party_Sts_Cd INTEGER,

Party_Sts_Rsn_Cd INTEGER,

Party_Sts_Chng_Dt DATE FORMAT 'YYYY-MM-DD',

Party_Prev_Sts_Cd INTEGER,

Party_Verif_Type_Cd INTEGER,

Bkrpt_Type_Cd INTEGER,

Cltrl_Hold_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Rvlv_Ln_Utlz_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Funds_Utlz_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Rtl_Liab_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

OD_Fac_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Cr_Line_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

New_To_Prod_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Swp_Ind_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Pref_Brnch_Party_Id INTEGER,

Last_Tran_Dt DATE FORMAT 'YYYY-MM-DD',

Home_Brnch_Party_Id INTEGER,

Risk_Grade_Id INTEGER,

Sspt_Frd_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Extr_Frd_Flg CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

UCF_Rec_Load_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

UCF_Rec_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

UCF_Rec_Deltd_Flg BYTEINT NOT NULL COMPRESS (0 ,1 ),

UCF_Proc_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_File_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_Trfm_Id INTEGER NOT NULL DEFAULT 0 )

PRIMARY INDEX ( Party_Id ,Eff_Start_Dt );

Statistics are defined on the below columns.

Party_Id

Eff_Start_Dt

Eff_End_Dt

Host_Party_Id

Party_Start_Dt

Lifcyc_Type_Cd

Init_Data_Src_Type_Cd

Orgn_Ctr_Cd

Party_Sts_Cd

Party_Sts_Rsn_Cd

Party_Prev_Sts_Cd

Party_Verif_Type_Cd

Bkrpt_Type_Cd

Pref_Brnch_Party_Id

Home_Brnch_Party_Id

Risk_Grade_Id

Party_Id,Eff_Start_Dt

CREATE SET TABLE EDMSIT01_T3D_FND.DIM_LOCTR ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Loctr_Id INTEGER NOT NULL,

Eff_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Eff_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

Loctr_Sbtyp_Cd INTEGER,

Addr_Sbtyp_Cd INTEGER,

Elctrnc_Addr_Sbtyp_Cd INTEGER,

Elctrnc_Addr_Txt VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Elctrnc_Addr_Dom_Nm VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Dom_Root_Cd INTEGER,

Phn_Num VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Phn_Country_Cd_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Phn_Area_Cd_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Phn_Exch_Num VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

Phn_Ln_Num VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

Phn_Extn_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Addr_Ln_1_Txt VARCHAR(500) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Addr_Ln_2_Txt VARCHAR(500) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Addr_Ln_3_Txt VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Dwlng_Type_Cd INTEGER,

Cnsus_Blk_Id_Geo INTEGER,

Tambol_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Tambol_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Country_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Country_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Region_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Region_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Province_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Province_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

City_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

City_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Amphur_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Amphur_Nm_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Carr_Rte_Txt VARCHAR(200) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Postal_Cd_Id INTEGER,

Hse_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Hse_Num_Mod_Val VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Street_Drctn_Type_Cd INTEGER,

Street_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Street_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Soi_Num VARCHAR(50) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Moo_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Street_Sfx_Cd INTEGER,

Bldg_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Bldg_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Unit_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Floor_Val VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Wrkspc_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Village_Nm VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Rte_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Mail_Pckup_Tm VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Mail_Delv_Tm VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ,

Mail_Stop_Num VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

Mail_Box_Num VARCHAR(16) CHARACTER SET UNICODE NOT CASESPECIFIC COMPRESS ,

UCF_Rec_Load_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

UCF_Rec_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

UCF_Rec_Deltd_Flg BYTEINT NOT NULL COMPRESS (0 ,1 ),

UCF_Proc_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_File_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_Trfm_Id INTEGER NOT NULL DEFAULT 0 )

PRIMARY INDEX ( Loctr_Id ,Eff_Start_Dt );

Statistics are defined on the below columns.

Loctr_Id

Eff_Start_Dt

Eff_End_Dt

Loctr_Sbtyp_Cd

Addr_Sbtyp_Cd

Elctrnc_Addr_Sbtyp_Cd

Dom_Root_Cd

Dwlng_Type_Cd

Cnsus_Blk_Id_Geo

Postal_Cd_Id

Street_Drctn_Type_Cd

Street_Sfx_Cd

Loctr_Id,Eff_Start_Dt

CREATE SET TABLE EDMSIT01_T3D_FND.DIM_PARTY_LOCTR ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Party_Id INTEGER NOT NULL,

Loctr_Seq_Num SMALLINT NOT NULL,

Eff_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Eff_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

RM_Ofcl_Phn_Num_Loctr_Id INTEGER,

RM_Home_PhnNum_Loctr_Id INTEGER,

RM_Ofcl_Fax_Num_Loctr_Id INTEGER,

RM_Persl_Mobl_Num_Loctr_Id INTEGER,

RM_Rsdnl_Addr_Loctr_Id INTEGER,

RM_Busn_Addr_Loctr_Id INTEGER,

RM_Comml_Addr_Loctr_Id INTEGER,

RM_Ofcl_Email_Addr_Loctr_Id INTEGER,

CIM_Cntct_Addr_Loctr_Id INTEGER,

CIM_Busn_Ofcl_Phn_Num_Loctr_Id INTEGER,

CIM_Persl_Mobl_Num_Loctr_Id INTEGER,

UCF_Rec_Load_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE ,

UCF_Rec_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL DEFAULT DATE '9999-12-31' COMPRESS (DATE '9999-12-31'),

UCF_Rec_Deltd_Flg BYTEINT NOT NULL COMPRESS (0 ,1 ),

UCF_Proc_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_File_Id INTEGER NOT NULL DEFAULT 0 ,

UCF_Trfm_Id INTEGER NOT NULL DEFAULT 0 )

PRIMARY INDEX ( Party_Id ,Loctr_Seq_Num ,Eff_Start_Dt );

Statistics are defined on the below columns.

Party_Id

Loctr_Seq_Num

Eff_Start_Dt

Eff_End_Dt

Party_Id,Loctr_Seq_Num,Eff_Start_Dt

Party_Id,RM_Ofcl_Phn_Num_Loctr_Id

Party_Id,RM_Home_PhnNum_Loctr_Id

Party_Id,RM_Ofcl_Fax_Num_Loctr_Id

Party_Id,RM_Persl_Mobl_Num_Loctr_Id

Party_Id,RM_Rsdnl_Addr_Loctr_Id

Party_Id,RM_Busn_Addr_Loctr_Id

Party_Id,RM_Comml_Addr_Loctr_Id

Party_Id,RM_Ofcl_Email_Addr_Loctr_Id

Party_Id,CIM_Cntct_Addr_Loctr_Id

Party_Id,CIM_Busn_Ofcl_Phn_Num_Loctr_Id

Party_Id,CIM_Persl_Mobl_Num_Loctr_Id

CREATE SET TABLE EDMSIT01_T2D_MDL.EDM_CLNDR_DT ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO

(

Clndr_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Dt_Chr CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Day_Of_Wk BYTEINT NOT NULL,

Clndr_Day_Of_Mth BYTEINT NOT NULL,

Clndr_Day_Of_Qtr BYTEINT NOT NULL,

Clndr_Day_Of_Yr SMALLINT NOT NULL,

Clndr_Wk_Of_Mth BYTEINT NOT NULL,

Clndr_Wk_Of_Qtr BYTEINT NOT NULL,

Clndr_Wk_Of_Yr BYTEINT NOT NULL,

Clndr_Mth_Of_Qtr BYTEINT NOT NULL,

Clndr_Mth_Of_Yr BYTEINT NOT NULL,

Clndr_Qtr_Of_Yr BYTEINT NOT NULL,

Clndr_Yr SMALLINT NOT NULL,

Clndr_Yr_TH SMALLINT NOT NULL,

Clndr_Day_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Day_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Wk_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_Nm VARCHAR(35) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Desc VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Start_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_End_Dt DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Day_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Day_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Wk_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Wk_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Mth_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Mth_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Qtr_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Qtr_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_Nm_TH VARCHAR(35) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Desc_TH VARCHAR(100) CHARACTER SET UNICODE NOT CASESPECIFIC NOT NULL,

Clndr_Yr_Start_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Clndr_Yr_End_Dt_TH DATE FORMAT 'YYYY-MM-DD' NOT NULL,

Day_Of_Clndr INTEGER NOT NULL,

Clndr_Days_In_Mth BYTEINT NOT NULL,

Clndr_Days_In_Qtr BYTEINT NOT NULL,

Clndr_Days_In_Yr SMALLINT NOT NULL,

Unix_Dt DECIMAL(18,6) NOT NULL)

UNIQUE PRIMARY INDEX UPI_GREGORIAN_DATE ( Clndr_Dt )

INDEX ( Clndr_Dt_Chr );

Statistics are defined on the below columns

Clndr_Dt

Clndr_Dt_Chr

The below query is taking long time to execute. Any kind of performance improvement suggestion is welcomed. Please help me out.

SELECT

DP.Party_Id,

ECD.Clndr_Dt,

DL.Soi_Num AS RM_Cust_Res_Soi_Num,

DL.Street_Nm AS RM_Cust_Res_Street_Nm ,

DL.Moo_Num AS RM_Cust_Res_Moo_Num,

DL.Addr_Ln_1_Txt AS RM_Cust_Res_Addr_Ln_1_Txt,

DL.Addr_Ln_2_Txt AS RM_Cust_Res_Addr_Ln_2_Txt,

DL.Addr_Ln_3_Txt AS RM_Cust_Res_Addr_Ln_3_Txt,

DL.Tambol_Nm_TH AS RM_Cust_Res_Tambol_Nm_TH,

DL.Amphur_Nm_TH AS RM_Cust_Res_Amphur_Nm_TH,

DL.Province_Nm_TH AS RM_Cust_Res_Province_Nm_TH,

L.Country_Nm_TH AS RM_Cust_Res_Country_Nm_TH,

DL.Postal_Cd_Id AS RM_Cust_Res_Postal_Cd_Id,

DL2.Phn_Num AS RM_Home_Phn_Num,

DL3.Elctrnc_Addr_Txt AS Customer_electronic_Address,

DL4.Phn_Num AS RM_Off_Phn_Num,

DL5.Phn_Num AS RM_Mobl_Phn_Num,

DL6.Phn_Num AS CIM_Off_Phn_Num,

DL7.Phn_Num AS CIM_Mobl_Phn_Num,

DL8.Addr_Ln_1_Txt AS RM_Cust_Bus_Addr_Ln_1_Txt,

DL8.Addr_Ln_2_Txt AS RM_Cust_Bus_Addr_Ln_2_Txt,

DL8.Addr_Ln_3_Txt AS RM_Cust_Bus_Addr_Ln_3_Txt,

DL8.Tambol_Nm_TH AS RM_Cust_Bus_Tambol_Nm_TH,

DL8.Amphur_Nm_TH AS RM_Cust_Bus_Amphur_Nm_TH,

DL8.Province_Nm_TH AS RM_Cust_Bus_Province_Nm_TH,

DL8.Country_Nm_TH AS RM_Cust_Bus_Country_Nm_TH,

DL8.Postal_Cd_Id AS RM_Cust_Bus_Postal_Cd_Id,

DL9.Addr_Ln_1_Txt AS CIM_Cust_Con_Addr_Ln_1_Txt,

DL9.Addr_Ln_2_Txt AS CIM_Cust_Con_Addr_Ln_2_Txt,

DL9.Addr_Ln_3_Txt AS CIM_Cust_Con_Addr_Ln_3_Txt,

DL9.Province_Nm_TH AS CIM_Cust_Con_Province_Nm_TH,

DL9.Country_Nm_TH AS CIM_Cust_Con_Country_Nm_TH,

DL9.Postal_Cd_Id AS CIM_Cust_Con_Postal_Cd_Id

FROM

EDMSIT01_T3V_FND.DIM_PARTY DP

INNER JOIN EDMSIT01_T2V_MDL.EDM_CLNDR_DT ECD

ON ECD.Clndr_Dt BETWEEN DP.Eff_Start_Dt AND DP.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_PARTY_LOCTR DPL

ON DPL.Party_Id = DP.Party_Id

AND ECD.Clndr_Dt BETWEEN DPL.Eff_Start_Dt AND DPL.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL

ON DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL.Eff_Start_Dt AND DL.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL2

ON DL2.Loctr_Id = DPL.RM_Home_PhnNum_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL2.Eff_Start_Dt AND DL2.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL3

ON DL3.Loctr_Id = DPL.RM_Ofcl_Email_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL3.Eff_Start_Dt AND DL3.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL4

ON DL4.Loctr_Id = DPL.RM_Ofcl_Phn_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL4.Eff_Start_Dt AND DL4.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL5

ON DL5.Loctr_Id = DPL.RM_Persl_Mobl_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL5.Eff_Start_Dt AND DL5.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL6

ON DL6.Loctr_Id = DPL.CIM_Busn_Ofcl_Phn_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL6.Eff_Start_Dt AND DL6.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL7

ON DL7.Loctr_Id = DPL.CIM_Persl_Mobl_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL7.Eff_Start_Dt AND DL7.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL8

ON DL8.Loctr_Id = DPL.RM_Busn_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL8.Eff_Start_Dt AND DL8.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL9

ON DL9.Loctr_Id = DPL.CIM_Cntct_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL9.Eff_Start_Dt AND DL9.Eff_End_Dt

Where Clndr_Dt = DATE '2012-09-21'

;

Tags (1)
6 REPLIES
Enthusiast

Re: Help on Query Tunning

Hi,

Since you are harcoding the date value we can avoid the join with the calender table

depending on the volume of the data involved if its is not really high then probably we can try an approach involving cross join instead of multiple instances of left join to the same table on diff columns

Some thing like this-

SELECT

DP.Party_Id,

cast('2012-09-21'  as Date) Clndr_Dt,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Soi_Num else null end),' '))AS RM_Cust_Res_Soi_Num ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Street_Nm else null end),' '))AS RM_Cust_Res_Street_Nm ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Moo_Num else null end),' '))AS RM_Cust_Res_Moo_Num ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Addr_Ln_1_Txt else null end),' '))AS RM_Cust_Res_Addr_Ln_1_Txt ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Addr_Ln_2_Txt else null end),' '))AS RM_Cust_Res_Addr_Ln_2_Txt ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Addr_Ln_3_Txt else null end),' '))AS RM_Cust_Res_Addr_Ln_3_Txt ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Tambol_Nm_TH else null end),' '))AS RM_Cust_Res_Tambol_Nm_TH ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Amphur_Nm_TH else null end),' '))AS RM_Cust_Res_Amphur_Nm_TH ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Province_Nm_TH else null end),' '))AS RM_Cust_Res_Province_Nm_TH ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Country_Nm_TH else null end),' '))AS RM_Cust_Res_Country_Nm_TH ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id then DL.Postal_Cd_Id else null end),' '))AS RM_Cust_Res_Postal_Cd_Id ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Home_PhnNum_Loctr_Id then DL.Phn_Num else null end),' '))AS RM_Home_Phn_Num ,

Max(coalesce( (case when DL.Loctr_Id = DPL.RM_Ofcl_Email_Addr_Loctr_Id then DL.Elctrnc_Addr_Txt else null end),' '))AS Customer_electronic_Address

from

EDMSIT01_T3V_FND.DIM_PARTY DP

LEFT JOIN EDMSIT01_T3V_FND.DIM_PARTY_LOCTR DPL

ON DPL.Party_Id = DP.Party_Id

and DPL.Eff_Start_Dt <= '2012-09-21' and DPL.Eff_End_Dt >= '2012-09-21'

cross join  EDMSIT01_T3V_FND.DIM_LOCTR DL

Where

DP.Eff_Start_Dt <= '2012-09-21'

and

DP.Eff_End_Dt >= '2012-09-21'

and

DL.Eff_Start_Dt <= '2012-09-21'

and

DL.Eff_End_Dt >= '2012-09-21'

;

Please let me know if this was helpful

Thanks

R.Rajeev

JGP
Enthusiast

Re: Help on Query Tunning

@Rajeev

Thanks for looking into the issue.

The volume of data is huge and the query provided by you made it worse.

Enthusiast

Re: Help on Query Tunning

Hi,

I agree cross join is not ideal when data volume is large sorry for that will try to find some other alternative

Regards

R.Rajeev

Junior Contributor

Re: Help on Query Tunning

Could you post the explain and ifo about the actual number of rows?

#1:

as Rajeev mentioned

"ECD.Clndr_Dt" can be simply replaced by "DATE '2012-09-21' AS CIndr_Dt"

and then remove all the joins to the calendar table like

AND Clndr_Dt BETWEEN DL4.Eff_Start_Dt AND DL4.Eff_End_Dt

Btw, you should collect stats on *all* columns in your calendar table (it's not helpfull for this query but might be for other)

#2:

Your PIs doesn't seem to be based on join/access as they include the Eff_Start_Dt. 

And they are defined as NUPI, but i suppose they actually are the logical Primary Key.

You should consider changing them to:

DIM_PARTY -> PRIMARY INDEX ( Party_Id  )

DIM_PARTY_LOCTR -> PRIMARY INDEX ( Party_Id )

DIM_LOCTR  -> PRIMARY INDEX ( Loctr_Id )

But check the existing stats on Party_Id/Loctr_Id if the number of rows per value is not too high, i.e. low double digit. If it's higher you might need to change SET to MULTISET.

Dieter
JGP
Enthusiast

Re: Help on Query Tunning

Hi Dieter,

Please find the explain plan below.

Explain SELECT

DP.Party_Id,

ECD.Clndr_Dt,

DL.Soi_Num AS RM_Cust_Res_Soi_Num,

DL.Street_Nm AS RM_Cust_Res_Street_Nm ,

DL.Moo_Num AS RM_Cust_Res_Moo_Num,

DL.Addr_Ln_1_Txt AS RM_Cust_Res_Addr_Ln_1_Txt,

DL.Addr_Ln_2_Txt AS RM_Cust_Res_Addr_Ln_2_Txt,

DL.Addr_Ln_3_Txt AS RM_Cust_Res_Addr_Ln_3_Txt,

DL.Tambol_Nm_TH AS RM_Cust_Res_Tambol_Nm_TH,

DL.Amphur_Nm_TH AS RM_Cust_Res_Amphur_Nm_TH,

DL.Province_Nm_TH AS RM_Cust_Res_Province_Nm_TH,

DL.Country_Nm_TH AS RM_Cust_Res_Country_Nm_TH,

DL.Postal_Cd_Id AS RM_Cust_Res_Postal_Cd_Id,

DL2.Phn_Num AS RM_Home_Phn_Num,

DL3.Elctrnc_Addr_Txt AS Customer_electronic_Address,

DL4.Phn_Num AS RM_Off_Phn_Num,

DL5.Phn_Num AS RM_Mobl_Phn_Num,

DL6.Phn_Num AS CIM_Off_Phn_Num,

DL7.Phn_Num AS CIM_Mobl_Phn_Num,

DL8.Addr_Ln_1_Txt AS RM_Cust_Bus_Addr_Ln_1_Txt,

DL8.Addr_Ln_2_Txt AS RM_Cust_Bus_Addr_Ln_2_Txt,

DL8.Addr_Ln_3_Txt AS RM_Cust_Bus_Addr_Ln_3_Txt,

DL8.Tambol_Nm_TH AS RM_Cust_Bus_Tambol_Nm_TH,

DL8.Amphur_Nm_TH AS RM_Cust_Bus_Amphur_Nm_TH,

DL8.Province_Nm_TH AS RM_Cust_Bus_Province_Nm_TH,

DL8.Country_Nm_TH AS RM_Cust_Bus_Country_Nm_TH,

DL8.Postal_Cd_Id AS RM_Cust_Bus_Postal_Cd_Id,

DL9.Addr_Ln_1_Txt AS CIM_Cust_Con_Addr_Ln_1_Txt,

DL9.Addr_Ln_2_Txt AS CIM_Cust_Con_Addr_Ln_2_Txt,

DL9.Addr_Ln_3_Txt AS CIM_Cust_Con_Addr_Ln_3_Txt,

DL9.Province_Nm_TH AS CIM_Cust_Con_Province_Nm_TH,

DL9.Country_Nm_TH AS CIM_Cust_Con_Country_Nm_TH,

DL9.Postal_Cd_Id AS CIM_Cust_Con_Postal_Cd_Id

FROM

EDMSIT01_T3V_FND.DIM_PARTY DP

INNER JOIN EDMSIT01_T2V_MDL.EDM_CLNDR_DT ECD

ON ECD.Clndr_Dt BETWEEN DP.Eff_Start_Dt AND DP.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_PARTY_LOCTR DPL

ON DPL.Party_Id = DP.Party_Id

AND ECD.Clndr_Dt BETWEEN DPL.Eff_Start_Dt AND DPL.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL

ON DL.Loctr_Id = DPL.RM_Rsdnl_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL.Eff_Start_Dt AND DL.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL2

ON DL2.Loctr_Id = DPL.RM_Home_PhnNum_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL2.Eff_Start_Dt AND DL2.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL3

ON DL3.Loctr_Id = DPL.RM_Ofcl_Email_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL3.Eff_Start_Dt AND DL3.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL4

ON DL4.Loctr_Id = DPL.RM_Ofcl_Phn_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL4.Eff_Start_Dt AND DL4.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL5

ON DL5.Loctr_Id = DPL.RM_Persl_Mobl_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL5.Eff_Start_Dt AND DL5.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL6

ON DL6.Loctr_Id = DPL.CIM_Busn_Ofcl_Phn_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL6.Eff_Start_Dt AND DL6.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL7

ON DL7.Loctr_Id = DPL.CIM_Persl_Mobl_Num_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL7.Eff_Start_Dt AND DL7.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL8

ON DL8.Loctr_Id = DPL.RM_Busn_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL8.Eff_Start_Dt AND DL8.Eff_End_Dt

LEFT JOIN EDMSIT01_T3V_FND.DIM_LOCTR DL9

ON DL9.Loctr_Id = DPL.CIM_Cntct_Addr_Loctr_Id

AND ECD.Clndr_Dt BETWEEN DL9.Eff_Start_Dt AND DL9.Eff_End_Dt

Where Clndr_Dt = DATE '2012-09-21'

;

  1) First, we lock EDMSIT01_T3D_FND.DIM_LOCTR in view

     EDMSIT01_T3V_FND.DIM_LOCTR for access, we lock

     EDMSIT01_T3D_FND.DIM_PARTY_LOCTR in view

     EDMSIT01_T3V_FND.DIM_PARTY_LOCTR for access, and we lock

     EDMSIT01_T3D_FND.DIM_PARTY in view EDMSIT01_T3V_FND.DIM_PARTY for

     access. 

  2) Next, we do a single-AMP RETRIEVE step from

     EDMSIT01_T2D_MDL.EDM_CLNDR_DT in view

     EDMSIT01_T2V_MDL.EDM_CLNDR_DT by way of the unique primary index

     "EDMSIT01_T2D_MDL.EDM_CLNDR_DT in view

     EDMSIT01_T2V_MDL.EDM_CLNDR_DT.Clndr_Dt = DATE '2012-09-21'" with

     no residual conditions locking row for access into Spool 14

     (all_amps) (compressed columns allowed), which is duplicated on

     all AMPs.  The size of Spool 14 is estimated with high confidence

     to be 72 rows (1,224 bytes).  The estimated time for this step is

     0.02 seconds. 

  3) We execute the following steps in parallel. 

       1) We do an all-AMPs JOIN step from Spool 14 (Last Use) by way

          of an all-rows scan, which is joined to

          EDMSIT01_T3D_FND.DIM_PARTY in view EDMSIT01_T3V_FND.DIM_PARTY

          by way of an all-rows scan with a condition of (

          "(EDMSIT01_T3D_FND.DIM_PARTY in view

          EDMSIT01_T3V_FND.DIM_PARTY.UCF_Rec_End_Dt = DATE

          '9999-12-31') AND ((EDMSIT01_T3D_FND.DIM_PARTY in view

          EDMSIT01_T3V_FND.DIM_PARTY.Eff_End_Dt >= DATE '2012-09-21')

          AND (EDMSIT01_T3D_FND.DIM_PARTY in view

          EDMSIT01_T3V_FND.DIM_PARTY.Eff_Start_Dt <= DATE

          '2012-09-21'))").  Spool 14 and EDMSIT01_T3D_FND.DIM_PARTY

          are joined using a product join, with a join condition of (

          "(Clndr_Dt <= EDMSIT01_T3D_FND.DIM_PARTY.Eff_End_Dt) AND

          (Clndr_Dt >= EDMSIT01_T3D_FND.DIM_PARTY.Eff_Start_Dt)").  The

          result goes into Spool 15 (all_amps) (compressed columns

          allowed), which is redistributed by the hash code of (

          EDMSIT01_T3D_FND.DIM_PARTY.Party_Id) to all AMPs.  Then we do

          a SORT to order Spool 15 by row hash.  The size of Spool 15

          is estimated with no confidence to be 2,767,675 rows (

          69,191,875 bytes).  The estimated time for this step is 15.64

          seconds. 

       2) We do an all-AMPs RETRIEVE step from

          EDMSIT01_T3D_FND.DIM_PARTY_LOCTR in view

          EDMSIT01_T3V_FND.DIM_PARTY_LOCTR by way of an all-rows scan

          with a condition of ("(EDMSIT01_T3D_FND.DIM_PARTY_LOCTR in

          view EDMSIT01_T3V_FND.DIM_PARTY_LOCTR.UCF_Rec_End_Dt = DATE

          '9999-12-31') AND ((EDMSIT01_T3D_FND.DIM_PARTY_LOCTR in view

          EDMSIT01_T3V_FND.DIM_PARTY_LOCTR.Eff_End_Dt >= DATE

          '2012-09-21') AND (EDMSIT01_T3D_FND.DIM_PARTY_LOCTR in view

          EDMSIT01_T3V_FND.DIM_PARTY_LOCTR.Eff_Start_Dt <= DATE

          '2012-09-21'))") into Spool 16 (all_amps) (compressed columns

          allowed), which is redistributed by the hash code of (

          EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.Party_Id) to all AMPs.  Then

          we do a SORT to order Spool 16 by row hash.  The size of

          Spool 16 is estimated with no confidence to be 7,960,501 rows

          (517,432,565 bytes).  The estimated time for this step is

          4.55 seconds. 

       3) We do an all-AMPs RETRIEVE step from

          EDMSIT01_T3D_FND.DIM_LOCTR in view EDMSIT01_T3V_FND.DIM_LOCTR

          by way of an all-rows scan with a condition of (

          "(EDMSIT01_T3D_FND.DIM_LOCTR in view

          EDMSIT01_T3V_FND.DIM_LOCTR.UCF_Rec_End_Dt = DATE

          '9999-12-31') AND ((EDMSIT01_T3D_FND.DIM_LOCTR in view

          EDMSIT01_T3V_FND.DIM_LOCTR.Eff_Start_Dt <= DATE '2012-09-21')

          AND (EDMSIT01_T3D_FND.DIM_LOCTR in view

          EDMSIT01_T3V_FND.DIM_LOCTR.Eff_End_Dt >= DATE '2012-09-21'))")

          into Spool 17 (all_amps) (compressed columns allowed), which

          is redistributed by the hash code of (

          EDMSIT01_T3D_FND.DIM_LOCTR.Loctr_Id) to all AMPs.  Then we do

          a SORT to order Spool 17 by row hash.  The size of Spool 17

          is estimated with no confidence to be 4,377,919 rows (

          5,748,207,647 bytes).  The estimated time for this step is 2

          minutes and 17 seconds. 

  4) We do an all-AMPs JOIN step from Spool 15 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 16 (Last Use) by way

     of a RowHash match scan.  Spool 15 and Spool 16 are left outer

     joined using a merge join, with condition(s) used for non-matching

     on left table ("UCF_Rec_End_Dt = DATE '9999-12-31'"), with a join

     condition of ("(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >=

     Eff_Start_Dt) AND ((UCF_Rec_End_Dt = UCF_Rec_End_Dt) AND (Party_Id

     = Party_Id )))").  The result goes into Spool 18 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (

     EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.RM_Persl_Mobl_Num_Loctr_Id) to

     all AMPs.  Then we do a SORT to order Spool 18 by row hash.  The

     size of Spool 18 is estimated with no confidence to be 6,258,311

     rows (381,756,971 bytes).  The estimated time for this step is 1

     minute and 3 seconds. 

  5) We do an all-AMPs JOIN step from Spool 17 by way of a RowHash

     match scan, which is joined to Spool 18 (Last Use) by way of a

     RowHash match scan.  Spool 17 and Spool 18 are right outer joined

     using a merge join, with condition(s) used for non-matching on

     right table ("(NOT (RM_Persl_Mobl_Num_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = RM_Persl_Mobl_Num_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 20 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.RM_Home_PhnNum_Loctr_Id)

     to all AMPs.  Then we do a SORT to order Spool 20 by row hash. 

     The size of Spool 20 is estimated with no confidence to be

     2,745,723 rows (343,215,375 bytes).  The estimated time for this

     step is 1 minute and 12 seconds. 

  6) We do an all-AMPs JOIN step from Spool 17 by way of a RowHash

     match scan, which is joined to Spool 20 (Last Use) by way of a

     RowHash match scan.  Spool 17 and Spool 20 are right outer joined

     using a merge join, with condition(s) used for non-matching on

     right table ("(NOT (RM_Home_PhnNum_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = RM_Home_PhnNum_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 24 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (

     EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.CIM_Busn_Ofcl_Phn_Num_Loctr_Id)

     to all AMPs.  Then we do a SORT to order Spool 24 by row hash. 

     The size of Spool 24 is estimated with no confidence to be

     1,204,638 rows (227,676,582 bytes).  The estimated time for this

     step is 57.59 seconds. 

  7) We do an all-AMPs JOIN step from Spool 17 by way of a RowHash

     match scan, which is joined to Spool 24 (Last Use) by way of a

     RowHash match scan.  Spool 17 and Spool 24 are right outer joined

     using a merge join, with condition(s) used for non-matching on

     right table ("(NOT (CIM_Busn_Ofcl_Phn_Num_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = CIM_Busn_Ofcl_Phn_Num_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 28 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (

     EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.CIM_Persl_Mobl_Num_Loctr_Id) to

     all AMPs.  Then we do a SORT to order Spool 28 by row hash.  The

     size of Spool 28 is estimated with no confidence to be 528,514

     rows (133,714,042 bytes).  The estimated time for this step is

     33.21 seconds. 

  8) We do an all-AMPs JOIN step from Spool 17 by way of a RowHash

     match scan, which is joined to Spool 28 (Last Use) by way of a

     RowHash match scan.  Spool 17 and Spool 28 are right outer joined

     using a merge join, with condition(s) used for non-matching on

     right table ("(NOT (CIM_Persl_Mobl_Num_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = CIM_Persl_Mobl_Num_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 32 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (

     EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.RM_Ofcl_Phn_Num_Loctr_Id) to all

     AMPs.  Then we do a SORT to order Spool 32 by row hash.  The size

     of Spool 32 is estimated with no confidence to be 231,877 rows (

     73,505,009 bytes).  The estimated time for this step is 17.86

     seconds. 

  9) We do an all-AMPs JOIN step from Spool 17 by way of a RowHash

     match scan, which is joined to Spool 32 (Last Use) by way of a

     RowHash match scan.  Spool 17 and Spool 32 are right outer joined

     using a merge join, with condition(s) used for non-matching on

     right table ("(NOT (RM_Ofcl_Phn_Num_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = RM_Ofcl_Phn_Num_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 36 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (

     EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.RM_Ofcl_Email_Addr_Loctr_Id) to

     all AMPs.  Then we do a SORT to order Spool 36 by row hash.  The

     size of Spool 36 is estimated with no confidence to be 101,732

     rows (38,759,892 bytes).  The estimated time for this step is 9.17

     seconds. 

 10) We execute the following steps in parallel. 

      1) We do an all-AMPs JOIN step from Spool 17 by way of a RowHash

         match scan, which is joined to Spool 36 (Last Use) by way of a

         RowHash match scan.  Spool 17 and Spool 36 are right outer

         joined using a merge join, with condition(s) used for

         non-matching on right table ("(NOT

         (RM_Ofcl_Email_Addr_Loctr_Id IS NULL )) AND (UCF_Rec_End_Dt =

         DATE '9999-12-31')"), with a join condition of ("(Clndr_Dt <=

         Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND ((Loctr_Id =

         RM_Ofcl_Email_Addr_Loctr_Id) AND (UCF_Rec_End_Dt =

         UCF_Rec_End_Dt )))").  The result goes into Spool 39

         (all_amps) (compressed columns allowed), which is

         redistributed by the hash code of (

         EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.RM_Rsdnl_Addr_Loctr_Id) to

         all AMPs.  The size of Spool 39 is estimated with no

         confidence to be 44,634 rows (22,852,608 bytes).  The

         estimated time for this step is 0.83 seconds. 

      2) We do an all-AMPs RETRIEVE step from

         EDMSIT01_T3D_FND.DIM_LOCTR in view EDMSIT01_T3V_FND.DIM_LOCTR

         by way of an all-rows scan with a condition of (

         "(EDMSIT01_T3D_FND.DIM_LOCTR in view

         EDMSIT01_T3V_FND.DIM_LOCTR.UCF_Rec_End_Dt = DATE '9999-12-31')

         AND ((EDMSIT01_T3D_FND.DIM_LOCTR in view

         EDMSIT01_T3V_FND.DIM_LOCTR.Eff_Start_Dt <= DATE '2012-09-21')

         AND (EDMSIT01_T3D_FND.DIM_LOCTR in view

         EDMSIT01_T3V_FND.DIM_LOCTR.Eff_End_Dt >= DATE '2012-09-21'))")

         locking for access into Spool 42 (all_amps) (compressed

         columns allowed), which is redistributed by the hash code of (

         EDMSIT01_T3D_FND.DIM_LOCTR.Loctr_Id) to all AMPs.  The size of

         Spool 42 is estimated with no confidence to be 4,377,919 rows

         (5,362,950,775 bytes).  The estimated time for this step is 2

         minutes and 35 seconds. 

 11) We do an all-AMPs JOIN step from Spool 39 (Last Use) by way of an

     all-rows scan, which is joined to Spool 42 (Last Use) by way of an

     all-rows scan.  Spool 39 and Spool 42 are left outer joined using

     a product join, with condition(s) used for non-matching on left

     table ("(NOT (RM_Rsdnl_Addr_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = RM_Rsdnl_Addr_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 43 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.RM_Busn_Addr_Loctr_Id)

     to all AMPs.  Then we do a SORT to order Spool 43 by row hash. 

     The size of Spool 43 is estimated with no confidence to be 19,583

     rows (33,369,432 bytes).  The estimated time for this step is

     18.09 seconds. 

 12) We do an all-AMPs JOIN step from Spool 43 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 17 by way of a

     RowHash match scan.  Spool 43 and Spool 17 are left outer joined

     using a merge join, with condition(s) used for non-matching on

     left table ("(NOT (RM_Busn_Addr_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = RM_Busn_Addr_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 45 (all_amps)

     (compressed columns allowed), which is redistributed by the hash

     code of (EDMSIT01_T3D_FND.DIM_PARTY_LOCTR.CIM_Cntct_Addr_Loctr_Id)

     to all AMPs.  Then we do a SORT to order Spool 45 by row hash. 

     The size of Spool 45 is estimated with no confidence to be 8,592

     rows (23,894,352 bytes).  The estimated time for this step is 5.38

     seconds. 

 13) We do an all-AMPs JOIN step from Spool 45 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 17 (Last Use) by way

     of a RowHash match scan.  Spool 45 and Spool 17 are left outer

     joined using a merge join, with condition(s) used for non-matching

     on left table ("(NOT (CIM_Cntct_Addr_Loctr_Id IS NULL )) AND

     (UCF_Rec_End_Dt = DATE '9999-12-31')"), with a join condition of (

     "(Clndr_Dt <= Eff_End_Dt) AND ((Clndr_Dt >= Eff_Start_Dt) AND

     ((Loctr_Id = CIM_Cntct_Addr_Loctr_Id) AND (UCF_Rec_End_Dt =

     UCF_Rec_End_Dt )))").  The result goes into Spool 13 (all_amps),

     which is built locally on the AMPs.  The size of Spool 13 is

     estimated with no confidence to be 3,770 rows (14,047,020 bytes). 

     The estimated time for this step is 0.76 seconds. 

 14) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 13 are sent back to the user as the result

     of statement 1.  The total estimated time is 9 minutes and 29

     seconds. 

Actually the select statement is defined as a view. So we cannot hardcode the date value. When the user runs the query on view, the date value will be passed.

I tried changing the below also.

DIM_PARTY -> PRIMARY INDEX ( Party_Id  )

DIM_PARTY_LOCTR -> PRIMARY INDEX ( Party_Id )

DIM_LOCTR  -> PRIMARY INDEX ( Loctr_Id )


But no help.

Junior Contributor

Re: Help on Query Tunning

So this was not the actual query :-(

Of course this is different now. 

This view is dangerous:

What if a user uses multiple dates or omits the WHERE-condition?

Each qualifying row from each table will be returned multiple times (once per date) resulting in a stupid cross-join.

IMHO this should be better implemented as a Macro/SP.

Dieter