RE: Natural Join Bug in 11.2.0.1 ??
Date: Sun, 24 Feb 2013 09:55:15 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CF0202CEE5_at_WIN02.hotsos.com>
Wow it's really worse then you make it look. Running a "normal" join there are 55 rows returned.
There is a significant difference in the plans. The natural join is joining on both DEPTMENT_ID and MANAGER_ID, hence anyone with w null manager ID is being tossed out. Maybe that is what you want I don't know but casually looking at it I would have thought of just a join on DEPARTMENT_ID. This is the Normal join plan:
SQL> get e_d_1.sql
1 SELECT EMP.EMPLOYEE_ID, EMP.DEPARTMENT_ID, DEPT.DEPARTMENT_NAME
FROM EMPLOYEES EMP, DEPARTMENTS DEPT
2 WHERE EMP.DEPARTMENT_ID = DEPT.DEPARTMENT_ID and DEPT.DEPARTMENT_ID
in (10,20,30,40,50)
3* ORDER BY EMP.DEPARTMENT_ID, EMP.EMPLOYEE_ID
4
SQL>
SQL> _at_hxplan
Enter .sql file name (without extension): e_d_1
Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] :
Plan hash value: 2480766633
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 22 |
506 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 22 |
506 | 5 (20)| 00:00:01 |
| 2 | NESTED LOOPS | | |
| | |
| 3 | NESTED LOOPS | | 22 |
506 | 4 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | |
| | |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 5 |
80 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 5 |
| 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 |
| 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 4 |
28 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------ -----------------------------
Predicate Information (identified by operation id):
6 - access("DEPT"."DEPARTMENT_ID"=10 OR "DEPT"."DEPARTMENT_ID"=20 OR "DEPT"."DEPARTMENT_ID"=30 OR "DEPT"."DEPARTMENT_ID"=40 OR "DEPT"."DEPARTMENT_ID"=50) 7 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID") filter("EMP"."DEPARTMENT_ID"=10 OR "EMP"."DEPARTMENT_ID"=20 OR "EMP"."DEPARTMENT_ID"=30 OR "EMP"."DEPARTMENT_ID"=40 OR"EMP"."DEPARTMENT_ID"=50)
SQL> This is the natural join plan:
SQL> get e_d_2.sql
1 SELECT EMPLOYEE_ID, DEPARTMENT_ID, DEPARTMENT_NAME FROM EMPLOYEES 2 NATURAL JOIN DEPARTMENTS where DEPARTMENT_ID in (10,20,30,40,50) 3* ORDER BY DEPARTMENT_ID, EMPLOYEE_ID 4
SQL>
SQL> _at_hxplan
Enter .sql file name (without extension): e_d_2 Enter the display level (TYPICAL, ALL, BASIC, SERIAL) [TYPICAL] : Plan hash value: 2480766633
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 2 |
60 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 2 |
60 | 4 (25)| 00:00:01 |
| 2 | NESTED LOOPS | | |
| | |
| 3 | NESTED LOOPS | | 2 |
60 | 3 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | |
| | |
|* 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 2 | 38 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 5 |
| 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 4 |
| 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 11 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------ -----------------------------
Predicate Information (identified by operation id):
5 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL) 6 - access("DEPARTMENTS"."DEPARTMENT_ID"=10 OR "DEPARTMENTS"."DEPARTMENT_ID"=20 OR "DEPARTMENTS"."DEPARTMENT_ID"=30 OR "DEPARTMENTS"."DEPARTMENT_ID"=40 OR "DEPARTMENTS"."DEPARTMENT_ID"=50) 7 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID") filter("EMPLOYEES"."DEPARTMENT_ID"=10 OR "EMPLOYEES"."DEPARTMENT_ID"=20 OR "EMPLOYEES"."DEPARTMENT_ID"=30 OR "EMPLOYEES"."DEPARTMENT_ID"=40 OR "EMPLOYEES"."DEPARTMENT_ID"=50)8 - filter("EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale
Sent: Sunday, February 24, 2013 10:16 AM
To: ORACLE-L
Subject: Natural Join Bug in 11.2.0.1 ??
While creating some sample SQLs for my OCA 11g students, I found what
seems to be a bug in
11.2.0.1 Oracle OTN Developer Days VM
select employee_id, department_id, department_name from employees
natural join departments where department_id in (10,20,30,40,50) order
by department_id, employee_id /
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ------------------------------ 202 20 Marketing 115 30 Purchasing 116 30 Purchasing 117 30 Purchasing 118 30 Purchasing 119 30 Purchasing 129 50 Shipping 130 50 Shipping 131 50 Shipping 132 50 Shipping 184 50 Shipping 185 50 Shipping 186 50 Shipping 187 50 Shipping
14 rows selected.
Strangely, these two are not reported with my data : (This looks like a
Bug)
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ------------------------------ 200 10 Administration 203 40 Human Resources
SQL> l
1 select e.employee_id, e.department_id, d.department_name
2 from employees e, departments d
3 where e.department_id = d.department_id
4* and d.department_id in (10,40)
SQL> /
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ------------------------------ 200 10 Administration 203 40 Human Resources
SQL>
SQL> l
1 select e.employee_id, e.department_id, d.department_name
2 from employees e, departments d
3 where e.department_id = d.department_id
4* and e.department_id in (10,40)
SQL> /
EMPLOYEE_ID DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------- ------------------------------ 200 10 Administration 203 40 Human Resources
SQL>
-- Hemant K Chitale http://hemantoracledba.blogspot.com http://hemantscribbles.blogspot.com http://web.singnet.com.sg/~hkchital -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sun Feb 24 2013 - 16:55:15 CET