Server times out when GROUP BY has more than one group

[Mike Mohr]

Server is timing out (180 sec) on this:


SELECT de.dept_no, f_avg_salary(de.dept_no, 'F', 50000, 100000)

AS AVG_FEMALE_SALARY,

f_avg_salary(de.dept_no, 'M', 50000, 100000)

AS AVG_MALE_SALARY

FROM t_dept_emp de

GROUP BY de.dept_no;



In an attempt to isolate the problem, I filtered by de.dept_no:



SELECT de.dept_no, f_avg_salary(de.dept_no, 'F', 50000, 100000)

AS AVG_FEMALE_SALARY,

f_avg_salary(de.dept_no, 'M', 50000, 100000)

AS AVG_MALE_SALARY

FROM t_dept_emp de

WHERE de.dept_no = 'd003' -- Filter by de.dept_no

GROUP BY de.dept_no;



Result appears normal for any ONE department which shows that the function is working (duration time is 1.25 sec). But when I try to include more than one department as shown above, or by including other departments with OR conditions in the WHERE clause, or by including multiple departments in an IN phrase, the server times out.