《[精选]ORACLE分析函数》由会员分享，可在线阅读，更多相关《[精选]ORACLE分析函数（41页珍藏版）》请在金锄头文库上搜索。
1、Oracle Analytic Functions In Practice,By dingjun123 2013.06,http:/,主要内容,甲：我有个SQL，你能帮我用分析函数改写下吗？ 乙：相关列有索引吗？ 甲：owner有索引，选择性不错，我想用分析函数改写看看？ 乙：哦，知道了，这是典型的top-n查询。,SELECT owner,object_type FROM demo2 WHERE owner=DINGJUN123 AND trunc(created,dd) = (SELECT MAX(trunc(created,dd) FROM demo2 WHERE owner=DINGJ
2、UN123),SELECT owner,object_type FROM ( SELECT owner,object_type, dense_rank() over(ORDER BY trunc(created,dd) DESC) rn FROM demo2 WHERE owner=DINGJUN123 ) WHERE rn=1,分析函数作用,欢迎进入今天的 分析函数学习之旅！,4,子查询方法,总行数：667827 返回9行 原始SQL : 逻辑读848，COST：1103 优点：最容易想到 缺点：多次访问表或索引,分析函数方法,分析SQL : 逻辑读423，COST：693 优点：减少表或索
3、引的访问次数，逻辑读和COST 是常规方法的一半，SQL简单 缺点：需要排序操作,进一步优化,建立owner,trunc(created,dd) desc复合索引 作用对于原始SQL子查询可以快速扫描，分析函数消除排序 优化后原始SQL逻辑读/COST：111/171,分析函数:6/767,构建合适索引，消除 排序，是一种重要的 SQL优化手段,- | Id | Operation | Name | Rows | - | 0 | SELECT STATEMENT | | 202 | |* 1 | TABLE ACCESS BY INDEX ROWID | DEMO2 | 202 | |* 2
4、| 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),fmdd)= (SELECT MAX(TRUNC(INTERNAL_F
5、UNCTION(CREATED),fmdd) FROM DEMO2 DEMO2 WHERE OWNER=DINGJUN123) 2 - access(OWNER=DINGJUN123) 5 - access(OWNER=DINGJUN123),- | Id | Operation | Name | Rows | - | 0 | SELECT STATEMENT | | 20237 | |* 1 | VIEW | | 20237 | |* 2 | WINDOW SORT PUSHED RANK | | 20237 | | 3 | TABLE ACCESS BY INDEX ROWID| DEMO
6、2 | 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),fmdd) DESC )=1) 4 - access(OWNER=DINGJUN123),分析函数作用,分析函数的作用总结,- | Id | Operation | Name | Rows |
7、 - | 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=DINGJUN123 AND trunc(created,dd) = (SELECT MAX(t
8、runc(created,dd) FROM demo2 WHERE owner=DINGJUN123),1.减少表或索引的访问次数,SELECT owner,object_type FROM ( SELECT owner,object_type, dense_rank() over(ORDER BY trunc(created,dd) DESC) rn FROM demo2 WHERE owner=DINGJUN123 ) WHERE rn=1,- | Id | Operation | Name | Rows | - | 0 | SELECT STATEMENT | | 21334 | |*
9、1 | VIEW | | 21334 | |* 2 | WINDOW NOSORT STOPKEY | | 21334 | | 3 | TABLE ACCESS BY INDEX ROWID| DEMO2 | 21334 | |* 4 | INDEX RANGE SCAN | IDX_DEMO2 | 21334 | -,2.实现复杂的行间计算，复杂聚合等,SELECT empno,sal,deptno, SUM(sal) over (PARTITION BY deptno ORDER BY empno) sum_current FROM emp,EMPNO SAL DEPTNO SUM_CUR
10、RENT - - - - 7782 2450 10 2450 7839 5000 10 7450 7934 1300 10 8750 。,很多分析函数要求排序,SELECT a.ID,a.sal,a.ext FROM t1 a, (SELECT ID,MAX(sal) max_sal FROM t1 GROUP BY ID ) b WHERE a.sal=b.max_sal AND a.ID=b.ID,SELECT ID,sal,ext FROM ( SELECT ID,sal,ext,rank() over(PARTITION BY ID ORDER BY sal DESC) rn FROM
11、 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:01.49,- | SELECT STATEMENT | | 990K| 49M| | | VIEW | | 990K| 49M|
12、 | | WINDOW SORT PUSHED RANK| | 990K| 36M| 49M| | TABLE ACCESS FULL | T1 | 990K| 36M| | -Elapsed: 00:00:04.38,CREATE TABLE t1 AS SELECT mod(LEVEL,1000) ID,LEVEL+1000 sal,MOD(LEVEL,10) ext FROM dual CONNECT BY LEVEL1000000,需要排序的分析函数，会消耗一定的资源，当然大多可以优化，对复杂的行间计算、累计值、移动平均等还是首选分析函数,ID很多重复，inline view分组减少连
13、接数量,分析函数缺点,分析函数语法-图1,分析函数语法-图2,PARTITION BY ORDER BY,ROWS Vs RANGE,Analytic Function,UNBOUNDED PRECEDING FOLLOWING CURRENT ROW,分析函数语法之关系,文档注意点和限制,分析函数语法之partition by,通过partition by子句，将相同的行聚合到一起成为一组，之后当前行的分析函数计算 就是在这行对应的partition里。每个分析函数都可以使用partition by子句。 每行在对应的窗口内，应用分析函数，然后计算得到当前行对应的分析函数值。 partiti
14、on by子句可以没有，如果也没有order by子句，那么表示当前行对应的窗口 范围是所有行。,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 320
15、25 20 7566 2975 10875 32025 20 7876 1100 10875 32025 20 7369 800 10875 32025 20 7788 3000 10875 32025,分析函数语法之order by,order by当前行默认窗口是当前行所属的partition第1行到当前行（根据order by顺序指定），无order by就是对应所属partition所有行。 order by默认是range窗口，对应逻辑窗口，保证分析函数值的唯一性，但是对排名分析函数特殊,因为排名函数不能带window。 order by如果有多个排序键且是range窗口，则必须要求
16、对应的窗口是当前partition所有行、第1行到当前行、当前行到当前partition最后一行或当前行到当前行,1,2,3,SELECT deptno, empno, sal, SUM(sal) over(PARTITION BY deptno ORDER BY sal) dept_current, SUM(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN unbounded preceding AND CURRENT ROW) dept_current1 FROM emp,DEPTNO EMPNO SAL DEPT_CURRENT DEPT_CURRENT1 - - - - - 10 7934 1300 1300 1300 10 7782 2450 3750 3750 10 7839 5000 8750 8750 20 7369 800 800 800 20 7876 1100 1900 1900 20 7566 2975 4875 4875 20 7788 3000 10875 10875 20 7902 3000
