Using same table twice in FROM Clause - Performance Concerns
782117Jun 29 2010 — edited Jun 30 2010Hello,
I'm using the same table twice in the FROM clause and I'm concerned if its even a good practice to do so. Will there be any performance issues?
I checked the explain plan and its scannign the table twice but by primary key.
Below is a sample of what I'm trying.
CREATE TABLE test_employee
(employee_id NUMBER PRIMARY KEY,
employee_name VARCHAR2(50)
);
INSERT INTO test_employee VALUES (1000, 'Richard');
INSERT INTO test_employee VALUES (2000, 'Mark');
COMMIT;
CREATE TABLE employee_relation
(primary_emp_id NUMBER,
secondary_emp_id NUMBER);
INSERT INTO employee_relation VALUES (1000, 2000);
COMMIT;
/*-----------------------------------------------------------------------------
--In this sql I'm using the same table twice in FROM Clause
--to get the name of primary and secondary user
------------------------------------------------------------------------------*/
SELECT rel.primary_emp_id,
pri_emp.employee_name primary_emp_name,
rel.secondary_emp_id,
sec_emp.employee_name secondary_emp_name
FROM employee_relation rel,
test_employee pri_emp,
test_employee sec_emp
WHERE rel.secondary_emp_id = 2000
AND pri_emp.employee_id = primary_emp_id
AND sec_emp.employee_id = secondary_emp_id;
Thanks,
Sandy