SQL query is slow.
Following SQL query is running slow:
=============================
SELECT a.ben_no, '1' type_cd, :INCOME_CD INCOME_CD, ben_id ID, B.FULL_NAME_A NAME FROM cm_ben_val_data a, cm_beneficiaries b WHERE a.ben_no = b.ben_no and cmdb.f_Get_age_yy ( b.birth_dt , cmdbdt.f_hijcurrrev ) >= 18 and nvl( death_flag_cd , 0 ) = 0 AND ID_NAME_cHG_CD = 2 AND NVL (send_to_income_val, 0) = 0;
UNION ALL
SELECT a.ben_no, '2' type_cd, :INCOME_CD INCOME_CD, RLTV_ID ID, a.RLTV_NAME NAME FROM cm_ben_fam_struc a , cm_beneficiaries b WHERE a.ben_no = b.ben_no and cmdb.f_Get_age_yy ( b.birth_dt , cmdbdt.f_hijcurrrev ) >= 18 and death_cd = 0 and NVL (send_to_income_val, 0) = 0;
=============================
SELECT a.ben_no, '1' type_cd, :INCOME_CD INCOME_CD, ben_id ID, B.FULL_NAME_A NAME FROM cm_ben_val_data a, cm_beneficiaries b WHERE a.ben_no = b.ben_no and cmdb.f_Get_age_yy ( b.birth_dt , cmdbdt.f_hijcurrrev ) >= 18 and nvl( death_flag_cd , 0 ) = 0 AND ID_NAME_cHG_CD = 2 AND NVL (send_to_income_val, 0) = 0;
UNION ALL
SELECT a.ben_no, '2' type_cd, :INCOME_CD INCOME_CD, RLTV_ID ID, a.RLTV_NAME NAME FROM cm_ben_fam_struc a , cm_beneficiaries b WHERE a.ben_no = b.ben_no and cmdb.f_Get_age_yy ( b.birth_dt , cmdbdt.f_hijcurrrev ) >= 18 and death_cd = 0 and NVL (send_to_income_val, 0) = 0;
0