SQL Tunning -- BIG difference in result

   Recently I was stuying SQL tuning and found the following two    statements run at totally different speed with only a difference of    one WHERE clause: deptno=10; the one with it runs almost 20 times    faster than the one without this condition. The table has about    38000 rows and column "pinno" is unique. Here is the tkprof report    (please maximize your email viewer):  

 select pinno, name, sal, e.deptno from att e  where sal > (select avg(sal) from att where e.deptno=deptno)  and e.deptno=10
 and rownum<10
 order by pinno -- this one runs 1462 ms in sql/plus with timing on  

 call count cpu elapsed disk query current  rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

  Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 762 763 7 9
• ------ -------- ---------- ---------- ---------- ----------

  ---

  total 4 0.00 0.00 762 763 7 9

 Misses in library cache during parse: 0  Optimizer goal: CHOOSE
 Parsing user id: 20 (SCOTT)  

 Rows Execution Plan

• --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 9 SORT (ORDER BY) 9 COUNT (STOPKEY) 17 FILTER 348 TABLE ACCESS (FULL) OF 'ATT' 2586 SORT (AGGREGATE) 38578 TABLE ACCESS (FULL) OF 'ATT'

  ---

 select pinno, name, sal, e.deptno from att e  where sal>(select avg(sal) from att where e.deptno=deptno)  and rownum<10 ------- without "deptno=10";  order by pinno -- this one runs 27119 ms in sql/plus with timing on  

 call count cpu elapsed disk query current  rows

• ------ -------- ---------- ---------- ---------- ----------

  ---

  Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 2 0.00 0.00 19578 19605 107 9
• ------ -------- ---------- ---------- ---------- ----------

  ---

  total 4 0.00 0.00 19578 19605 107 9

 Misses in library cache during parse: 0  Optimizer goal: CHOOSE
 Parsing user id: 20 (SCOTT)  

 Rows Execution Plan

• --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 9 SORT (ORDER BY) 9 COUNT (STOPKEY) 26 FILTER 26 TABLE ACCESS (FULL) OF 'ATT' 66789 SORT (AGGREGATE) 1003028 TABLE ACCESS (FULL) OF 'ATT'

  ---

 They seem to have same execution plan but why such a big difference?  And why are CPU and elapsed time showing zero?  Could anyone give me a clue? Thanks a lot.

Alan Tang Received on Fri Feb 26 1999 - 17:35:14 CET