[计算机软件及应用]oracle优化实战2012.ppt

WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 自动分析函数Rollup 这样的SQL并不少见，以个税为例，统计查询中大部分功能的合计都是单独汇总的。表jcxx_nsrsrnsxx2的数据量很大，汇总SQL对这个表扫描了两遍，直接影响了查询速度。如果是分页查询的话，又增加一次统计，效率可想而知。 WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 自动分析函数Rollup 如果使用了自动分析函数Rollup，合计不用另行统计 SELECT nvl(t.ffyf, 合计) AS 所得期间, SUM(t.sre) AS 收入额, SUM(t.sjynse) AS 实际应纳税额, COUNT(DISTINCT t.zzlx_dm || ‘_’ || t.zzhm) AS 课税人数 FROM jcxx_nsrsrnsxx2 t GROUP BY ROLLUP(t.ffyf) WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 Rank系列函数 如果要从一批纳税信息中查找出每个月的纳税状元，你的SQL该怎么写？ WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 Rank系列函数 Rank系统函数中的row_number函数可以对各分组的记录分别赋予行号，那样SQL就简单了。 SELECT a.zzlx_dm AS 证照类型, a.zzhm AS 证照号码, a.xm AS 姓名, a.sjynse AS 实际应纳税额. FROM (SELECT t.zzlx_dm, t.zzhm, t.xm, t.sjynse, row_number() over(PARTITION BY t.ffyf ORDER BY t.sjynse DESC) gid FROM jcxx_nsrsrnsxx2 t) a WHERE a.gid = 1 WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 Lag和Lead函数 如果要计算每个月的税收增长率，SQL要怎么写？ WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 Lag和Lead函数 Lag函数可以取到当前行前第N行的记录，SQL可以简化为 SELECT a.ffyf AS 税款属期, a.sjynse AS 实际应纳税额, decode(lag(a.sjynse, 1, 0) over(ORDER BY a.ffyf), 0, 0, (a.sjynse - lag(a.sjynse, 1, 0) over(ORDER BY a.ffyf)) / lag(a.sjynse, 1, 0) over(ORDER BY a.ffyf) * 100) AS 增长率 FROM (SELECT t.ffyf, SUM(t.sjynse) sjynse FROM jcxx_nsrsrnsxx2 t GROUP BY t.ffyf) a WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 Lag和Lead函数 Lead函数用法与Lag一致，只是Lag是往前取，而Lead是往后取。 要注意排序字段的值是否连续，比如：ffyf缺少某一个月，那就需 设法补上该月的记录，然后才能计算月增长率。 WWW.SERVYOU.COM.CN 数据库编程优化 编码优化的措施及建议 处理数据方面 报表分析函数Ratio_to_report Ratio_to_report函数可以计算各条记录的某个数值在合计中的所占比例。比如：计算2