你宁选I/O少还是CPU花销少
例子是基于oracle数据库模板用户中的表emp与表dept：（如下）
题目要求：查询部门平均工资最高的部门名称和最低的部门名称。
（想起有人主张的是：尽量减少同一个表的多次访问，也即避免第一种方案）
这个题目有多种方案，这里举例了4种方案：
查询方案一：
scott@PROD>select name,sal from (select avg(e.sal) sal,d.dname name
2 from emp e,dept d where e.deptno = d.deptno group by d.dname)
3 where rownum<=1
4 union
5 select name,sal from (select avg(e.sal) sal,d.dname name
6 from emp e,dept d where e.deptno = d.deptno group by d.dname
7 order by 1 asc)
8 where rownum<=1;
NAME SAL
-------------- ----------
ACCOUNTING 2916.66667
SALES 1566.66667
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2982675878
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 19 (32)| 00:00:01 |
| 1 | SORT UNIQUE | | 2 | 44 | 19 (32)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 3 | 66 | 8 (25)| 00:00:01 |
|* 5 | SORT GROUP BY STOPKEY | | 3 | 123 | 8 (25)| 00:00:01 |
| 6 | NESTED LOOPS | | 3 | 123 | 7 (15)| 00:00:01 |
| 7 | NESTED LOOPS | | 3 | 123 | 7 (15)| 00:00:01 |
| 8 | VIEW | VW_GBC_6 | 3 | 87 | 4 (25)| 00:00:01 |
| 9 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 10 | TABLE ACCESS FULL | EMP | 15 | 105 | 3 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 12 | 1 (0)| 00:00:01 |
|* 13 | COUNT STOPKEY | | | | | |
| 14 | VIEW | | 5 | 110 | 9 (23)| 00:00:01 |
|* 15 | SORT ORDER BY STOPKEY | | 5 | 95 | 9 (23)| 00:00:01 |
| 16 | HASH GROUP BY | | 5 | 95 | 9 (23)| 00:00:01 |
|* 17 | HASH JOIN | | 15 | 285 | 7 (0)| 00:00:01 |
| 18 | TABLE ACCESS FULL | DEPT | 5 | 60 | 4 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | EMP | 15 | 105 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<=1)
5 - filter(ROWNUM<=1)
11 - access("ITEM_1"="D"."DEPTNO")
13 - filter(ROWNUM<=1)
15 - filter(ROWNUM<=1)
17 - access("E"."DEPTNO"="D"."DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
696 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
2 rows processed
查询方案二：
scott@PROD>with
2 t1 as (select avg(e.sal) sal,d.dname name
3 from emp e,dept d where e.deptno = d.deptno group by d.dname),
4 t2 as (select sal maxsal ,name maxname from t1 ),
5 t3 as (select sal minsal ,name minname from t1 order by 1 asc),
6 t4 as (Select maxsal,maxname, minsal,minname from t2,t3 where rownum<=1)
7 Select * from t4;
MAXSAL MAXNAME MINSAL MINNAME
---------- -------------- ---------- --------------
2916.66667 ACCOUNTING 1566.66667 SALES
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1644619083
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 10 (10)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6606_1FB984 | | | | |
| 3 | HASH GROUP BY | | 5 | 95 | 8 (13)| 00:00:01 |
|* 4 | HASH JOIN | | 15 | 285 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 5 | 60 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 15 | 105 | 3 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 44 | 2 (0)| 00:00:01 |
|* 8 | COUNT STOPKEY | | | | | |
| 9 | NESTED LOOPS | | 1 | 44 | 2 (0)| 00:00:01 |
| 10 | VIEW | | 1 | 22 | 1 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1FB984 | 5 | 95 | 2 (0)| 00:00:01 |
| 12 | VIEW | | 1 | 22 | 1 (0)| 00:00:01 |
| 13 | SORT ORDER BY | | 5 | 110 | 3 (34)| 00:00:01 |
| 14 | VIEW | | 5 | 110 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6606_1FB984 | 5 | 95 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
8 - filter(ROWNUM<=1)
Statistics
----------------------------------------------------------
9 recursive calls
9 db block gets
26 consistent gets
1 physical reads
576 redo size
788 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
查询方案三：
scott@PROD>with
2 q1 AS (SELECT e.deptno, d.dname, AVG(sal) AVG FROM emp e, dept d
3 WHERE e.deptno = d.deptno GROUP BY e.deptno, d.dname)
4 SELECT dname, AVG FROM q1
5 WHERE AVG = (SELECT MAX(AVG) FROM q1)
6 union all
7 SELECT dname, AVG FROM q1
8 WHERE AVG = (SELECT MIN(AVG) FROM q1);
DNAME AVG
-------------- ----------
ACCOUNTING 2916.66667
SALES 1566.66667
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 715795792
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 484 | 8 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6608_1FB984 | | | | |
| 3 | HASH GROUP BY | | 11 | 209 | 8 (13)| 00:00:01 |
|* 4 | HASH JOIN | | 15 | 285 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 5 | 60 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 15 | 105 | 3 (0)| 00:00:01 |
| 7 | UNION-ALL | | | | | |
|* 8 | VIEW | | 11 | 242 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_1FB984 | 11 | 209 | 2 (0)| 00:00:01 |
| 10 | SORT AGGREGATE | | 1 | 13 | | |
| 11 | VIEW | | 11 | 143 | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_1FB984 | 11 | 209 | 2 (0)| 00:00:01 |
|* 13 | VIEW | | 11 | 242 | 2 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_1FB984 | 11 | 209 | 2 (0)| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 13 | | |
| 16 | VIEW | | 11 | 143 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_1FB984 | 11 | 209 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
8 - filter("AVG"= (SELECT MAX("AVG") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"DEPTNO","C1" "DNAME","C2" "AVG" FROM "SYS"."SYS_TEMP_0FD9D6608_1FB984" "T1") "Q1"))
13 - filter("AVG"= (SELECT MIN("AVG") FROM (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0"
"DEPTNO","C1" "DNAME","C2" "AVG" FROM "SYS"."SYS_TEMP_0FD9D6608_1FB984" "T1") "Q1"))
Statistics
----------------------------------------------------------
9 recursive calls
8 db block gets
31 consistent gets
1 physical reads
576 redo size
697 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
查询方案四：
scott@PROD>with
2 t1 as
3 (select dname deptno, avg(sal) as avgsal
4 from emp d, dept t
5 where d.deptno = t.deptno group by dname),
6 t2 as
7 (select max(avgsal) as sal1, min(avgsal) sal2 from t1)
8 select d1.deptno, d2.sal1, d3.deptno, d4.sal2
9 from t1 d1, t2 d2, t1 d3, t2 d4
10 where d1.avgsal = d2.sal1
11 and d3.avgsal = d4.sal2;
DEPTNO SAL1 DEPTNO SAL2
-------------- ---------- -------------- ----------
ACCOUNTING 2916.66667 SALES 1566.66667
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3553761139
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 70 | 18 (6)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_1FB984 | | | | |
| 3 | HASH GROUP BY | | 5 | 95 | 8 (13)| 00:00:01 |
|* 4 | HASH JOIN | | 15 | 285 | 7 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 5 | 60 | 4 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP | 15 | 105 | 3 (0)| 00:00:01 |
| 7 | LOAD AS SELECT | SYS_TEMP_0FD9D660C_1FB984 | | | | |
| 8 | SORT AGGREGATE | | 1 | 13 | | |
| 9 | VIEW | | 5 | 65 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_1FB984 | 5 | 95 | 2 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 1 | 70 | 8 (0)| 00:00:01 |
| 12 | MERGE JOIN CARTESIAN | | 1 | 57 | 6 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 1 | 35 | 4 (0)| 00:00:01 |
| 14 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_1FB984 | 1 | 13 | 2 (0)| 00:00:01 |
| 16 | VIEW | | 5 | 110 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_1FB984 | 5 | 95 | 2 (0)| 00:00:01 |
| 18 | BUFFER SORT | | 5 | 110 | 6 (0)| 00:00:01 |
| 19 | VIEW | | 5 | 110 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_1FB984 | 5 | 95 | 2 (0)| 00:00:01 |
| 21 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_1FB984 | 1 | 13 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."DEPTNO"="T"."DEPTNO")
11 - access("D3"."AVGSAL"="D4"."SAL2")
13 - access("D1"."AVGSAL"="D2"."SAL1")
Statistics
----------------------------------------------------------
17 recursive calls
16 db block gets
42 consistent gets
2 physical reads
1108 redo size
782 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
以上4种方案都达到了题目的目的，各有各的优势。其实，当硬件方面CPU足够，可以牺牲CPU，
选择I/O少的方案，相反，CPU不够优越，则综合考虑。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31392094/viewspace-2128654/ ，如需转载，请注明出处，否则将追究法律责任。
