== Physical Plan == Project [xxxx_id#62 AS xxxx_id#2,tin#1 AS ssss#3,yyyy_id#0 AS yyyy_id#4,mmmm_zzzz_one#53 AS mmmm_zzzz_one#5,mmmm_zzzz_two#54 AS mmmm_zzzz_two#6,mmmm_zzzz_three#55 AS mmmm_zzzz_three#7,mmmm_zzzz_four#56 AS mmmm_zzzz_four#8,last_zzzz#57 AS last_zzzz#9,aaaa_zzzz_id#49 AS aaaa_zzzz_id#10,suffix_desc#59 AS suffix_desc#11,zzzz_usage_tp_cd#52 AS zzzz_usage_tp_cd#12,pppp_dt#31 AS pppp_dt#13,lst_xxxx#64 AS lst_xxxx#14,zzzzhip_type#99 AS zzzzhip_tp_cd#15,end_dt#61 AS aaaa_zzzz_end_dt#16,xxxx_state_tp_cd#107 AS xxxx_state_tp_cd#17,zzzz#108 AS state_of_ssss_type#18,xxxx_tp_cd#114 AS xxxx_tp_cd#19,zzzz#115 AS xxxx_of_llll_type#20,end_dt#101 AS xzzzzhip_end_dt#21,id_tp_cd#79 AS id_tp_cd#22] +- SortMergeOuterJoin [xxxx_of_llll_type#42], [xxxx_tp_cd#114], LeftOuter, None :- Sort [xxxx_of_llll_type#42 ASC], false, 0 : +- TungstenExchange hashpartitioning(xxxx_of_llll_type#42,200), None : +- Project [end_dt#101,zzzz#108,mmmm_zzzz_one#53,tin#1,xxxx_state_tp_cd#107,mmmm_zzzz_two#54,xxxx_id#62,suffix_desc#59,xxxx_of_llll_type#42,zzzz_usage_tp_cd#52,yyyy_id#0,aaaa_zzzz_id#49,mmmm_zzzz_four#56,end_dt#61,lst_xxxx#64,pppp_dt#31,last_zzzz#57,mmmm_zzzz_three#55,id_tp_cd#79,zzzzhip_type#99] : +- SortMergeOuterJoin [state_of_ssss_type#43], [xxxx_state_tp_cd#107], LeftOuter, None : :- Sort [state_of_ssss_type#43 ASC], false, 0 : : +- TungstenExchange hashpartitioning(state_of_ssss_type#43,200), None : : +- Project [end_dt#101,mmmm_zzzz_one#53,tin#1,mmmm_zzzz_two#54,xxxx_id#62,suffix_desc#59,xxxx_of_llll_type#42,zzzz_usage_tp_cd#52,yyyy_id#0,aaaa_zzzz_id#49,mmmm_zzzz_four#56,state_of_ssss_type#43,end_dt#61,lst_xxxx#64,pppp_dt#31,last_zzzz#57,mmmm_zzzz_three#55,id_tp_cd#79,zzzzhip_type#99] : : +- SortMergeOuterJoin [xxxx_id#62], [xxxx_id#98], LeftOuter, None : : :- Sort [xxxx_id#62 ASC], false, 0 : : : +- Project [mmmm_zzzz_one#53,tin#1,mmmm_zzzz_two#54,xxxx_id#62,suffix_desc#59,xxxx_of_llll_type#42,zzzz_usage_tp_cd#52,yyyy_id#0,aaaa_zzzz_id#49,mmmm_zzzz_four#56,state_of_ssss_type#43,end_dt#61,lst_xxxx#64,pppp_dt#31,last_zzzz#57,mmmm_zzzz_three#55,id_tp_cd#79] : : : +- SortMergeJoin [xxxx_id#24], [xxxx_id#78] : : : :- Project [mmmm_zzzz_one#53,mmmm_zzzz_two#54,xxxx_id#62,suffix_desc#59,xxxx_id#24,xxxx_of_llll_type#42,zzzz_usage_tp_cd#52,aaaa_zzzz_id#49,mmmm_zzzz_four#56,state_of_ssss_type#43,end_dt#61,lst_xxxx#64,pppp_dt#31,last_zzzz#57,mmmm_zzzz_three#55] : : : : +- SortMergeJoin [xxxx_id#24], [xxxx_id#62] : : : : :- Project [state_of_ssss_type#43,xxxx_of_llll_type#42,pppp_dt#31,xxxx_id#24] : : : : : +- SortMergeOuterJoin [xxxx_id#24], [xxxx_id#41], LeftOuter, None : : : : : :- Sort [xxxx_id#24 ASC], false, 0 : : : : : : +- TungstenExchange hashpartitioning(xxxx_id#24,200), None : : : : : : +- ConvertToUnsafe : : : : : : +- HiveTableScan [xxxx_id#24,pppp_dt#31], MetastoreRelation db, vw_aaaa, None : : : : : +- Sort [xxxx_id#41 ASC], false, 0 : : : : : +- TungstenExchange hashpartitioning(xxxx_id#41,200), None : : : : : +- ConvertToUnsafe : : : : : +- HiveTableScan [xxxx_id#41,state_of_ssss_type#43,xxxx_of_llll_type#42], MetastoreRelation db, vw_xaaaa, None : : : : +- Sort [xxxx_id#62 ASC], false, 0 : : : : +- TungstenExchange hashpartitioning(xxxx_id#62,200), None : : : : +- ConvertToUnsafe : : : : +- HiveTableScan [xxxx_id#62,mmmm_zzzz_one#53,mmmm_zzzz_two#54,mmmm_zzzz_three#55,mmmm_zzzz_four#56,last_zzzz#57,aaaa_zzzz_id#49,suffix_desc#59,zzzz_usage_tp_cd#52,lst_xxxx#64,end_dt#61], MetastoreRelation db, vw_aaaazzzz, None : : : +- Sort [xxxx_id#78 ASC], false, 0 : : : +- TungstenExchange hashpartitioning(xxxx_id#78,200), None : : : +- Project [CASE WHEN (cast(id_tp_cd#79 as double) = 2.0) THEN ref_num#80 ELSE null AS tin#1,id_tp_cd#79,xxxx_id#78,xx_id#0] : : : +- Filter (id_tp_cd#79 = 100000) : : : +- HiveTableScan [id_tp_cd#79,ref_num#80,xxxx_id#78], MetastoreRelation db, vw_identifier, None : : +- Sort [xxxx_id#98 ASC], false, 0 : : +- TungstenExchange hashpartitioning(xxxx_id#98,200), None : : +- ConvertToUnsafe : : +- Filter (length(end_dt#101) = 0) : : +- HiveTableScan [xxxx_id#98,zzzzhip_type#99,end_dt#101], MetastoreRelation db, vw_xzzzzhip, None : +- Sort [xxxx_state_tp_cd#107 ASC], false, 0 : +- TungstenExchange hashpartitioning(xxxx_state_tp_cd#107,200), None : +- ConvertToUnsafe : +- HiveTableScan [xxxx_state_tp_cd#107,zzzz#108], MetastoreRelation db, vw_cdprovstatetp, None +- Sort [xxxx_tp_cd#114 ASC], false, 0 +- TungstenExchange hashpartitioning(xxxx_tp_cd#114,200), None +- ConvertToUnsafe +- HiveTableScan [xxxx_tp_cd#114,zzzz#115], MetastoreRelation db, vw_cdxxxxtp, None ... View more