【正文】 甲：我有個(gè) SQL，你能幫我用分析函數(shù)改寫(xiě)下嗎？ 乙：相關(guān)列有索引嗎？ 甲： owner有索引 ， 選擇性不錯(cuò)，我想 用分析函數(shù)改寫(xiě)看看？ 乙：哦，知道了，這是典型的 topn查詢。 SELECT owner,object_type FROM demo2 WHERE owner=39。DINGJUN12339。 AND trunc(created,39。dd39。) = (SELECT MAX(trunc(created,39。dd39。)) FROM demo2 WHERE owner=39。DINGJUN12339。) SELECT owner,object_type FROM ( SELECT owner,object_type, dense_rank() over(ORDER BY trunc(created,39。dd39。) DESC) rn FROM demo2 WHERE owner=39。DINGJUN12339。 ) WHERE rn=1 4 子查詢方法 總行數(shù)： 667827 返回 9行 原始 SQL : 邏輯讀 848， COST： 1103 優(yōu)點(diǎn)：最容易想到 缺點(diǎn)：多次訪問(wèn)表或索引 分析函數(shù)方法 分析 SQL : 邏輯讀 423， COST： 693 優(yōu)點(diǎn)：減少表或索引的訪問(wèn)次數(shù) ，邏輯讀和 COST 是常規(guī)方法的一半， SQL簡(jiǎn)單 缺點(diǎn)：需要排序操作 | Id | Operation | Name | Rows | | 0 | SELECT STATEMENT | | 202 | |* 1 | TABLE ACCESS BY INDEX ROWID | DEMO2 | 202 | |* 2 | INDEX RANGE SCAN | IDX_DEMO2 | 20237 | | 3 | SORT AGGREGATE | | 1 | | 4 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 20237 | |* 5 | INDEX RANGE SCAN | IDX_DEMO2 | 20237 | Predicate Information (identified by operation id): 1 filter(TRUNC(INTERNAL_FUNCTION(CREATED),39。fmdd39。)= (SELECT MAX(TRUNC(INTERNAL_FUNCTION(CREATED),39。fmdd39。)) FROM DEMO2 DEMO2 WHERE OWNER=39。DINGJUN12339。)) 2 access(OWNER=39。DINGJUN12339。) 5 access(OWNER=39。DINGJUN12339。) | Id | Operation | Name | Rows | | 0 | SELECT STATEMENT | | 20237 | |* 1 | VIEW | | 20237 | |* 2 | WINDOW SORT PUSHED RANK | | 20237 | | 3 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 20237 | |* 4 | INDEX RANGE SCAN | IDX_DEMO2 | 20237 | Predicate Information (identified by operation id): 1 filter(RN=1) 2 filter(DENSE_RANK() OVER ( ORDER BY TRUNC(INTERNAL_FUNCTION(CREATED),39。fmdd39。) DESC )=1) 4 access(OWNER=39。DINGJUN12339。) | Id | Operation | Name | Rows | | 0 | SELECT STATEMENT | | 213 | | 1 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 213 | |* 2 | INDEX RANGE SCAN | IDX_DEMO2 | 1 | | 3 | SORT AGGREGATE | | 1 | |* 4 | INDEX RANGE SCAN | IDX_DEMO2 | 21334 | SELECT owner,object_type FROM demo2 WHERE owner=39。DINGJUN12339。 AND trunc(created,39。dd39。) = (SELECT MAX(trunc(created,39。dd39。)) FROM demo2 WHERE owner=39。DINGJUN12339。)SELECT owner,object_type FROM ( SELECT owner,object_type, dense_rank() over(ORDER BY trunc(created,39。dd39。) DESC) rn FROM demo2 WHERE owner=39。DINGJUN12339。 ) WHERE rn=1 | Id | Operation | Name | Rows | | 0 | SELECT STATEMENT | | 21334 | |* 1 | VIEW | | 21334 | |* 2 | WINDOW NOSORT STOPKEY | | 21334 | | 3 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 21334 | |* 4 | INDEX RANGE SCAN | IDX_DEMO2 | 21334 | SELECT empno,sal,deptno, SUM(sal) over (PARTITION BY deptno ORDER BY empno) sum_current FROM emp EMPNO SAL DEPTNO SUM_CURRENT 7782 2450 10 2450 7839 5000 10 7450 7934 1300 10 8750 。 SELECT , FROM t1 a, (SELECT ID,MAX(sal) max_sal FROM t1 GROUP BY ID ) b WHERE = AND = SELECT ID,sal,ext FROM ( SELECT ID,sal,ext,rank() over(PARTITION BY ID ORDER BY sal DESC) rn FROM t1 ) WHERE rn=1 | SELECT STATEMENT | | 1 | 65 | | | HASH JOIN | | 1 | 65 | 35M| | VIEW | | 990K| 24M| | | HASH GROUP BY | | 990K| 24M| | | TABLE ACCESS FULL| T1 | 990K| 24M| | | TABLE ACCESS FULL | T1 | 990K| 36M| | Elapsed: 00:00: | SELECT STATEMENT | | 990K| 49M| | | VIEW | | 990K| 49M| | | WINDOW SORT PUSHED RANK| | 990K| 36M| 49M| | TABLE ACCESS FULL | T1 | 990K| 36M| | Elapsed: 00:00: CREATE TABLE t1 AS SELECT mod(LEVEL,1000) ID,LEVEL+1000 sal,MOD(LEVEL,10) ext FROM dual CONNECT BY LEVEL1000000 PARTITION BY ORDER BY ROWS Vs RANGE Analytic Function UNBOUNDED PRECEDING FOLLOWING CURRENT ROW 通過(guò) partition by子句，將相同的行聚合到 一起成為一組，之后當(dāng)前行的分析函數(shù)計(jì)算 就是在這行對(duì)應(yīng)的 partition里。每個(gè) 分析函數(shù)都可以使用 partition by子句 。 每行在對(duì)應(yīng)的窗口內(nèi)，應(yīng)用分析函數(shù)，然后計(jì)算得到當(dāng)前行對(duì)應(yīng)的分析函數(shù)值。 partition by子句可以沒(méi)有，如果也沒(méi)有 order by子句，那么表示當(dāng)前行對(duì)應(yīng)的窗口 范圍是所有行。 1 1 2 2 3 SELECT deptno,empno,sal, SUM(sal) over(PARTITION BY deptno) sum_dept, SUM(sal) over() sum_all FROM emp DEPTNO EMPNO SAL SUM_DEPT SUM_ALL 10 7782 2450 8750 32025 10 7934 1300 8750 32025 10 7839 5000 8750 32025 20 7902 3000 10875 32025 20 7566 2975 10875 32025 20 7876 1100 10875 32025 20 7369 800 10875 32025 20 7788 3000 10875 32025 order by當(dāng)前行默認(rèn)窗口是 當(dāng)前行所屬的 partition第 1行到當(dāng) 前行 （ 根據(jù) order by順序指定 ） ， 無(wú) order by就是對(duì)應(yīng)所屬 partition所有行 。 order by默認(rèn)是 range窗口，對(duì)應(yīng)邏輯窗口，保證分析函數(shù)值的 唯一性，但是對(duì)排名分析函數(shù)特殊 ,因?yàn)榕琶瘮?shù)不能帶 window。 order by如果有多個(gè)排序 鍵且是 range窗口， 則