SQL & PL/SQL

Any idea why this code runs in 1.5 seconds:

SELECT ppf.employee_number,
ppf.full_name,
(SELECT COUNT(per.person_id)-1
FROM	 hr.per_all_people_f per
	,hr.per_person_type_usages_f usg
	,hr.per_person_types ppt
	,hr.per_all_assignments_f paf
WHERE	per.person_id = paf.person_id
AND	paf.primary_flag = 'Y'
AND	per.person_id = usg.person_id
AND	usg.person_type_id = ppt.person_type_id
AND	ppt.user_person_type = 'Employee'
AND	TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date	     
AND	TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND	TRUNC(SYSDATE) BETWEEN usg.effective_start_date AND usg.effective_end_date
AND	per.person_id IN
(SELECT v.person_id FROM (SELECT person_id, supervisor_id
			FROM hr.per_all_assignments_f s
			WHERE TRUNC(SYSDATE) BETWEEN s.effective_start_date AND s.effective_end_date 
			AND s.primary_flag = 'Y'
			AND s.assignment_type = 'E') v
CONNECT BY v.supervisor_id = PRIOR v.person_id
START WITH v.person_id = 136463)) "Subordinate Count"
FROM hr.per_all_people_f ppf
WHERE TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = 136463

... but this code takes 5 minutes to reach the same result?

SELECT ppf.employee_number,
ppf.full_name,
(SELECT COUNT(per.person_id)-1
FROM	 hr.per_all_people_f per
	,hr.per_person_type_usages_f usg
	,hr.per_person_types ppt
	,hr.per_all_assignments_f paf
WHERE	per.person_id = paf.person_id
AND	paf.primary_flag = 'Y'
AND	per.person_id = usg.person_id
AND	usg.person_type_id = ppt.person_type_id
AND	ppt.user_person_type = 'Employee'
AND	TRUNC(SYSDATE) BETWEEN per.effective_start_date AND per.effective_end_date	     
AND	TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND	TRUNC(SYSDATE) BETWEEN usg.effective_start_date AND usg.effective_end_date
AND	per.person_id IN
(SELECT v.person_id FROM (SELECT person_id, supervisor_id
			FROM hr.per_all_assignments_f s
			WHERE TRUNC(SYSDATE) BETWEEN s.effective_start_date AND s.effective_end_date 
			AND s.primary_flag = 'Y'
			AND s.assignment_type = 'E') v
CONNECT BY v.supervisor_id = PRIOR v.person_id
START WITH v.person_id = ppf.person_id)) "Subordinate Count"
FROM hr.per_all_people_f ppf
WHERE TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.person_id = 136463

The only difference is on line 23. Alternatively, do you have a better suggestion to obtain a subordinate count? Using database version 10g.

Edited by: user12159223 on Dec 1, 2009 9:27 AM

Edited by: user12159223 on Dec 1, 2009 9:28 AM