|
|
|
Forum Member
 
Group: Forum Members
Last Login: Today @ 1:21:33 AM
Posts: 28,
Visits: 147
|
|
Hi Guys
Can you please help me with a better approach to optimize the view below,
the main driving table is CBF_DELIVERY, I have tried to create subqueries but the explain still looks bad,
Can you please help.
REPLACE VIEW "DEVTCRMEIW"."VW_Campaign_Out"
AS
Select
T1.PARTY_ID
, T3.SB_Title_Desc
, T2.Family_Name
, T4.Address_Line_1_Txt As Mailing_Address_Line_1
, T4.Address_Line_2_Txt As Mailing_Address_Line_2
, T4.Address_Line_3_Txt As Mailing_Address_Line_3
, T4.SB_Address_Line_4_Txt As Mailing_Address_Line_4
, T4.Postal_Cd As Mailing_Posatal_Cd
, T5.Ext_Identification_Num
, T6.Age
, T6.Birth_Dt
, T6.Gender_Type_Cd
, T6.Ethnicity_Cd
, T7.Segment_Id
, T8.Contract_Name
, T8.Account_Open_Dt
, T8.O_Account_Num
, T9.BRI
, T10.Confidential_Limit
, T11.Center_id
, T11.Province_Id
, T11.Area_Id
, T12.Org_Name As Centre_Name
, T13.Org_Name As Province_Name
, T15.Org_Name As Area_Name
, T14.Privacy_Preference_Ind As Marketing_Ind
, T16.Work_Telephone_Number
, T16.Home_Telephone_Number
, T16.Cell_Telephone_Number
, T17.Prefered_Language_Cd
, T18.Dormant_Ind As Dormant_Ind
, T19.Business_Legal_Class_Cd As Enterprise_Type
, T20.SB_Exptd_Credt_Trnvr_Amt As Expected_Credit_TO
, T21.Number_Credit_Entry As Number_Credit_Entry_MTD
, T22.Number_Credit_Entry As Number_Credit_Entry_YTD
, T23.Number_Debit_Entry As Number_Debit_Entry_MTD
, T24.Number_Debit_Entry As Number_Debit_Entry_YTD
, T25.Fee_Amt_MTD
, T26.Fee_Amt_YTD
, T27.Product_Id
, T27.Product_Name
, 'E' As Business_Language_Cd
, 'CAAAAAA' As Account_Status
, '100000000' As Current_Limit
, 'HHHHHHHH' As Product_Holding
, '1000000.00' As Salary
, T28.Address_Line_1_Txt As Res_Address_Line_1
, T28.Address_Line_2_Txt As Res_Address_Line_2
, T28.Address_Line_3_Txt As Res_Address_Line_3
, T28.SB_Address_Line_4_Txt As Res_Address_Line_4
, T28.Postal_Cd As Res_Posatal_Cd
From
devtcrm.CBF_DELIVERY T1
INNER JOIN
devtcrmeiw.VW_Party_Names T2
ON
T1.Party_Id = T2.Party_Id
INNER JOIN
TESTEIW.SB_TITLE_TYPE T3
ON
T2.SB_Title_Cd = T3.SB_Title_Cd
INNER JOIN
devtcrmeiw.VW_Party_Mailing_Address T4
ON
T1.Party_Id = T4.Party_Id
INNER JOIN
devtcrmeiw.VW_Party_Identification T5
ON
T1.Party_Id = T5.Party_Id
INNER JOIN
devtcrmeiw.VW_Party_Demographic T6
ON
T1.Party_Id = T6.Party_Id
INNER JOIN
devtcrmeiw.VW_Party_Segment T7
ON
T1.Party_Id = T7.Party_Id
INNER JOIN
devtcrmeiw.VW_Acct_Agreement T8
ON
T1.Account_Num = T8.Account_Num
INNER JOIN
devtcrmeiw.VW_Acct_BRI T9
ON
T1.Account_Num = T9.Account_Num
INNER JOIN
devtcrmeiw.VW_Acct_Confidential_Limit T10
ON
T1.Account_Num = T10.Account_Num
INNER JOIN
VW_Acct_Controlling_Branch T11
ON
T1.Party_Id = T11.Party_Id
INNER JOIN
ORGANIZATION_NAME_HIST T12
ON
T11.Center_id = T12. Org_Party_Id
AND
T12.Org_Name_End_Dt is Null
AND
T12.Name_Type_Cd = 8
INNER JOIN
ORGANIZATION_NAME_HIST T13
ON
T11.Province_Id = T13.Org_Party_Id
AND
T13.Org_Name_End_Dt is Null
AND
T13.Name_Type_Cd = 12
INNER JOIN
ORGANIZATION_NAME_HIST T15
ON
T11.Area_Id = T15.Org_Party_Id
AND
T15.Org_Name_End_Dt is Null
AND
T15.Name_Type_Cd = 11
INNER JOIN
VW_Party_Int_Marketing T14
ON
T1.Party_Id = T14.Party_Id
INNER JOIN
VW_Party_Telephones T16
ON
T1.Party_Id = T16.Party_Id
INNER JOIN
VW_Party_Prefered_Language T17
ON
T1.Party_Id = T17.Party_Id
LEFT OUTER JOIN
VW_Acct_Dormant_Ind T18
ON
T1.Account_Num = T18.Account_Num
LEFT OUTER JOIN
VW_Party_EnterpriseType T19
ON
T1.Party_Id = T19.Party_Id
LEFT OUTER JOIN
VW_Acct_Mth_Credit_TO T20
ON
T1.Account_Num = T20.Account_Num
LEFT OUTER JOIN
VW_Acct_Day_Credit_Entry_MTD T21
ON
T1.Account_Num = T21.Account_Num
LEFT OUTER JOIN
VW_Acct_Day_Credit_Entry_YTD T22
ON
T1.Account_Num = T22.Account_Num
LEFT OUTER JOIN
VW_Acct_Day_Debit_Entry_MTD T23
ON
T1.Account_Num = T23.Account_Num
LEFT OUTER JOIN
VW_Acct_Day_Debit_Entry_YTD T24
ON
T1.Account_Num = T24.Account_Num
LEFT OUTER JOIN
VW_Acct_Day_Fee_Events_MTD T25
ON
T1.Account_Num = T25.Account_Num
LEFT OUTER JOIN
VW_Acct_Day_Fee_Events_YTD T26
ON
T1.Account_Num = T26.Account_Num
INNER JOIN
VW_Acct_Product T27
ON
T1.Account_Num = T27.Account_Num
INNER JOIN
VW_Party_Residential_Address T28
ON
T1.Party_Id = T28.Party_Id;
Thanx
Ince
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/23/2008 11:25:35 PM
Posts: 76,
Visits: 148
|
|
hi,
As with any join, the table size matters most and also the joining columns.
-> If possible have the primay indexes on respective tables as join columns
-> Join index is a worthy option to explore
-> for continuous inner joins b/w tables on same join columns - try to have the tables in increasing data volume - this way you can minimise the amount of data that goes into further joins.
thx
-SN
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Today @ 1:21:33 AM
Posts: 28,
Visits: 147
|
|
Thanks
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|