Optimizing View

Database
Enthusiast

Optimizing View

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
2 REPLIES
SN
Enthusiast

Re: Optimizing View

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
Enthusiast

Re: Optimizing View

Thanks