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
, 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
, 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
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.