现有如下 SQL 及其执行计划。

    1. select ename,deptno,sal
    2.     from emp a
    3.    where sal = (select max(sal) from emp b where a.deptno = b.deptno);
    1. Execution Plan
    2. ----------------------------------------------------------
    3. Plan hash value: 1245077725
    4. -------------------------------------------------------------------------------
    5. | Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
    6. -------------------------------------------------------------------------------
    7. |   0 | SELECT STATEMENT     |         |     1 |    39 |     8  (25)| 00:00:01 |
    8. |*  1 |  HASH JOIN           |         |     1 |    39 |     8  (25)| 00:00:01 |
    9. |   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     4  (25)| 00:00:01 |
    10. |   3 |    HASH GROUP BY     |         |     3 |    21 |     4  (25)| 00:00:01 |
    11. |   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     3   (0)| 00:00:01 |
    12. |   5 |   TABLE ACCESS FULL  | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
    13. -------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    1 - access("SAL"="MAX(SAL)" AND "A"."DEPTNO"="ITEM_1")

    该 SQL 表示查询员工表中每个部门工资最高的员工的所有信息，访问了 EMP 表两次。

    我们可以利用分析函数对上面 SQL 进行等价改写，使 EMP 只访问一次。

    分析函数的写法如下。

    1. select ename, deptno, sal
    2.     from (select a.*, max(sal) over(partition by deptno) max_sal from emp a)
    3.     where sal = max_sal;
    1. Execution Plan
    2. ----------------------------------------------------------
    3. Plan hash value: 4130734685
    4. ----------------------------------------------------------------------------
    5. | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    6. ----------------------------------------------------------------------------
    7. |   0 | SELECT STATEMENT    |      |    14 |   644 |     4  (25)| 00:00:01 |
    8. |*  1 |  VIEW               |      |    14 |   644 |     4  (25)| 00:00:01 |
    9. |   2 |   WINDOW SORT       |      |    14 |   182 |     4  (25)| 00:00:01 |
    10. |   3 |    TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
    11. ----------------------------------------------------------------------------
    13. Predicate Information (identified by operation id):
    14. ---------------------------------------------------
    16.    1 - filter("SAL"="MAX_SAL")

    使用分析函数改写之后，减少了表扫描次数，EMP 表越大，性能提升越明显。