Hi my query
SELECT *
FROM dis_pay_det_view;
taking 45 minutes.
The explain plain is as attached.
My View is as dis_pay_det_view as follows:
CREATE or REPLACE view dis_pay_det_view
(
Pay_ref ,
Pay_Type ,
Pay_Name ,
Pay_Bud_Cen ,
Pay_Value_Aug ,
Pay_Value_Sep ,
Pay_Value_Oct ,
Pay_Value_Nov ,
Pay_Value_Dec ,
Pay_Value_Jan ,
Pay_Value_Feb ,
Pay_Value_Mar ,
Pay_Value_Apr ,
Pay_Value_May ,
Pay_Value_Jun ,
Pay_Value_Jul
)
AS
SELECT distinct
si.si_pay_num ,
substr(si.si_analysis_code1,1,3) ,
-- get_staff_cost_type(si.si_analysis_code1) , --
e.e_name ,
decode(si.si_analysis_code3,
null,si.si_analysis_code1,si.si_analysis_code3) ,
mis_payroll_pack.period_value (
'AUG-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'SEP-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'OCT-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'NOV-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'DEC-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'JAN-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'FEB-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'MAR-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'APR-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'MAY-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'JUN-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) ) ,
mis_payroll_pack.period_value (
'JUL-06/07' ,
decode(si.si_analysis_code3,null,si_analysis_code1,si_analysis_code3) ,
si.si_pay_num,substr(si.si_analysis_code1,1,2) )
FROM mis_salary_items si ,
mis_employee e
WHERE si.si_control_ind = 'D'
AND si.si_pay_num = e.e_emp_ref
AND si.si_account_period like '%06/07%'
/
CREATE or REPLACE Package mis_payroll_pack AS
FUNCTION period_value
( account_period IN CHAR,
budget_centre IN CHAR,
staff_ref IN CHAR,
pay_type IN CHAR
)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES ( period_value, WNDS, WNPS );
FUNCTION period_value_nom
( account_period IN CHAR,
budget_centre IN CHAR,
nominal IN CHAR,
staff_ref IN CHAR
)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES ( period_value_nom, WNDS, WNPS );
END mis_payroll_pack;
/
rem Define Function Body.
CREATE or REPLACE Package body mis_payroll_pack AS
FUNCTION period_value
( account_period IN CHAR,
budget_centre IN CHAR,
staff_ref IN CHAR,
pay_type IN CHAR
)
RETURN NUMBER
IS
return_period_value NUMBER := 0 ;
BEGIN
SELECT nvl(sum(si.si_value), 0)
INTO return_period_value
FROM mis_salary_items si
WHERE si.si_account_period = account_period
AND decode(si.si_analysis_code3,
null , si.si_analysis_code1 , si.si_analysis_code3)
= budget_centre
AND si.si_pay_num = staff_ref
AND substr(si.si_analysis_code1,1,2) = pay_type
AND si.si_month_available = 'Y'
AND si.si_control_ind = 'D';
RETURN ( return_period_value );
EXCEPTION
WHEN NO_DATA_FOUND
THEN RETURN(0);
END;
/*
*/
FUNCTION period_value_nom
( account_period IN CHAR,
budget_centre IN CHAR,
nominal IN CHAR,
staff_ref IN CHAR
)
RETURN NUMBER
IS
return_period_value_nom NUMBER := 0 ;
BEGIN
SELECT nvl(sum(si.si_value), 0)
INTO return_period_value_nom
FROM mis_salary_items si
WHERE si.si_account_period = account_period
AND decode(si.si_analysis_code3,
null , si.si_analysis_code1 , si.si_analysis_code3)
= budget_centre
AND si.si_pay_num = staff_ref
AND si.si_analysis_code1 = nominal
AND si.si_month_available = 'Y'
AND si.si_control_ind = 'D';
RETURN ( return_period_value_nom );
EXCEPTION
WHEN NO_DATA_FOUND
THEN RETURN(0);
END;
END mis_payroll_pack;
/
Please find the attached explain plan file .
Please advice me to optimize my sql.