RE: Natural Join Bug in 11.2.0.1 ??

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
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-l
Received on Sun Feb 24 2013 - 16:55:15 CET

Original text of this message