www.Orasql.Com
 
用户名: 密码:  验证码: 注册|找回密码 oracleawen's blog  
 
         当前位置 >> 技术论坛  发表帖子     论坛版块     查找帖子
AUTOTRACE 验证消除子查询后的性能-技术论坛||Oracle高手之路—Orasql.Com!
            AUTOTRACE 验证消除子查询后的性能
 
发贴人:oracle_awen
oracleawen@gmail.com448315233127.0.0.1
用户级别:管理员
发贴总数:1245
注册时间:2011-05-05 09:23:03
2012-02-27 09:29:57

SQL> set autotrace traceonly;

SQL> select * from scott.emp e1 where e1.sal > (select avg(sal) from scott.emp e
2 where e2.deptno = e1.deptno);

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 3796349128

---------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    63 |    12   (9)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    37 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS              |               |     1 |    63 |    12   (9)| 00:00:01 |

|   3 |    VIEW                     | VW_SQ_1       |     3 |    78 |    10  (10)| 00:00:01 |

|   4 |     HASH GROUP BY           |               |     3 |    21 |    10  (10)| 00:00:01 |

|   5 |      TABLE ACCESS FULL      | EMP           |    14 |    98 |     9   (0)| 00:00:01 |

|*  6 |    INDEX RANGE SCAN         | SCOTT_INDEX_1 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"="E1"."DEPTNO")
   6 - access("E1"."SAL">"VW_COL_1")


统计信息
----------------------------------------------------------
        256  recursive calls
          0  db block gets
         57  consistent gets
          8  physical reads
          0  redo size
       1033  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          6  rows processed

 

 


SQL> ed
已写入 file afiedt.buf

  1  select * from scott.emp e1,(select e2.deptno deptno,
  2  avg(e2.sal) avg_sal from scott.emp e2 group by deptno) dept_avg_sal
  3  where e1.deptno=dept_avg_sal.deptno
  4* and e1.sal > dept_avg_sal.avg_sal
SQL> /

已选择6行。


执行计划
----------------------------------------------------------
Plan hash value: 3051581259

---------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |    63 |    12   (9)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    37 |     2   (0)| 00:00:01 |

|   2 |   NESTED LOOPS              |               |     1 |    63 |    12   (9)| 00:00:01 |

|   3 |    VIEW                     |               |     3 |    78 |    10  (10)| 00:00:01 |

|   4 |     HASH GROUP BY           |               |     3 |    21 |    10  (10)| 00:00:01 |

|   5 |      TABLE ACCESS FULL      | EMP           |    14 |    98 |     9   (0)| 00:00:01 |

|*  6 |    INDEX RANGE SCAN         | SCOTT_INDEX_1 |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("E1"."DEPTNO"="DEPT_AVG_SAL"."DEPTNO")
   6 - access("E1"."SAL">"DEPT_AVG_SAL"."AVG_SAL")


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1208  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

      
 
  
 Orasql.Com|E-Mail:oracleawen@gmail.com|Phone:15271942915|Copyright © 2011|All Rights Reserved|