Strange cost difference

From: Mladen Gogala <no_at_email.here.invalid>
Date: Thu, 17 Feb 2011 22:34:02 +0000 (UTC)
Message-ID: <pan.2011.02.17.22.34.02_at_email.here.invalid>

I was comparing 2 SQL statements which produce the same result. Here are the statements:

with e(empno,mgr,lev) as (
select empno,mgr,1 from emp where empno=7839 union all
select emp.empno,emp.mgr,e.lev+1
from emp,e
where emp.mgr=e.empno)
select * from e

select empno,mgr,level
from emp
connect by prior empno=mgr
start with empno=7839

I executed both statements with autotrace on and here is the problem: Execution Plan

---

Plan hash value: 2925328376

---

---

| Id | Operation | Name | Rows |
Bytes | Cos
t (%CPU)| Time |

---

---

| 0 | SELECT STATEMENT | | 15 |
585 |
 6 (17)| 00:00:56 |

| 1 | VIEW | | 15 |

585 |
 6 (17)| 00:00:56 |

| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| |

|	   |
	|	   |



|   3 |    TABLE ACCESS BY INDEX ROWID		  | EMP    |	 1 |	

17 |
 1 (0)| 00:00:11 |

|*  4 |     INDEX UNIQUE SCAN			  | PK_EMP |	 1 
|	   |

 0 (0)| 00:00:01 |

|* 5 | HASH JOIN | | 14 | 602 |
 5 (20)| 00:00:46 |

| 6 | RECURSIVE WITH PUMP | |

|	   |
	|	   |



|   7 |     TABLE ACCESS FULL			  | EMP    |	14 |   

238 |
 3 (0)| 00:00:31 |

---

---

Predicate Information (identified by operation id):

---

   4 - access("EMPNO"=7839)
   5 - access("EMP"."MGR"="E"."EMPNO")

Note

---

• SQL plan baseline "SQL_PLAN_c3fy5a6wn21t0f51d791e" used for this statement

The question is why does oracle think that this statement will return 15 rows? Statistics is there, the table is fully analyzed. With the "connect by" formulation, there is no such problem:

Execution Plan

---

Plan hash value: 763482334

---

---

| Id | Operation | Name | Rows | Bytes |
Cost (%
CPU)| Time |

---

---

| 0 | SELECT STATEMENT | | 14 | 364
| 4

(25)| 00:00:41 |

|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|      |       |       |
    |	       |



|   2 |   TABLE ACCESS FULL			| EMP  |    14 |   238 

|     3

 (0)| 00:00:31 |

---

---

Predicate Information (identified by operation id):

---

   1 - access("MGR"=PRIOR "EMPNO")

       filter("EMPNO"=7839)

Note

---

• SQL plan baseline "SQL_PLAN_7myzsfbbvmbs0acb9d868" used for this statement

Consequently, Oracle thinks that the 1st statement is more expensive. The version is 11.2.0.2. Recursive CTE are supported as of Oracle 11.2, so there wasn't much of a need to specify this. It won't work on 10G or lower.
Also, execution paths are showing internal optimization in both cases. I like the recursive CTE because I can start level with 5 or 3.14, if that's what's needed.

-- 
http://mgogala.byethost5.com

Received on Thu Feb 17 2011 - 16:34:02 CST