SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> set autotrace on
执行sql语句1:
SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=30
union all
SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=20;
union all
SELECT COUNT(*),SUM(sal) FROM emp WHERE deptno=20;
COUNT(*) SUM(SAL)
---------- ----------
6 9400
5 10875
Execution Plan
----------------------------------------------------------
Plan hash value: 2390793
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | SORT AGGREGATE | | 1 | 7 | | |
|* 3 | TABLE ACCESS FULL| EMP | 6 | 42 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 7 | | |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 14 | 6 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | SORT AGGREGATE | | 1 | 7 | | |
|* 3 | TABLE ACCESS FULL| EMP | 6 | 42 | 3 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 7 | | |
|* 5 | TABLE ACCESS FULL| EMP | 5 | 35 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
---------------------------------------------------
3 - filter("DEPTNO"=30)
5 - filter("DEPTNO"=20)
5 - filter("DEPTNO"=20)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
516 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)
2 rows processed
执行sql语句2(decode):
SQL> select count(decode(deptno,20,'x',null)) d20_count,
2 count(decode(deptno,30,'x',null)) d30_count,
3 sum(decode(deptno,20,sal,null)) d20_count,
4 sum(decode(deptno,30,sal,null)) d30_count
5 from emp
6 ;
2 count(decode(deptno,30,'x',null)) d30_count,
3 sum(decode(deptno,20,sal,null)) d20_count,
4 sum(decode(deptno,30,sal,null)) d30_count
5 from emp
6 ;
D20_COUNT D30_COUNT D20_COUNT D30_COUNT
---------- ---------- ---------- ----------
5 6 10875 9400
---------- ---------- ---------- ----------
5 6 10875 9400
Execution Plan
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
600 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)
1 rows processed
总结:sql语句1产生了14个逻辑读,sql语句2产生了7个逻辑读,很显然sql语句1的性能
远远低于sql2的性能.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8410760/viewspace-731999/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8410760/viewspace-731999/