今天，确切的讲应该是昨天下午，研发的同事突然向我寻求SQL技术支援。
看看他的问题，比较麻烦些。
他需要提取某个时间段内，某个ID(fee_terminal_id)最新时间的状态。其实如果是在一张表中，直接MAX(report_time)就可以了。但是，恰恰这是两张表。每张表中一个状态，通常的情况就是我们首先得到ID及其最大的（最新）报告时间，然后再“自连接”自己进行WHERE条件的查询。
上面的SQL就是通过这种基本的想法来实现的。
一般情况下，应该是没有问题的。但是，这次问题却出现了。主要就是数据太大，执行了30分钟没有看到有结果集返回。
首先因为两张基本表（XXX_log sbl, XXX_report smr）都是分区表，不然数据也不会这么大呀。进行必要的分区工作，要知道，再精良的SQL，数据太多也会有问题。优化的第一步就是“少做”（片面一点讲，性能真的是设计出来的）。
完成后，直接执行原始SQL，基本上180秒左右，可以看到结果集返回。
至此，都有些“跑题”，还有过滤重复呢（其实就是找出最新时间的ID状态即可）。
看着这句SQL我总是有些不死心，看看其实这句SQL的两个部分的SQL语句基本相同，其实一部分就可以得到所有信息，就是因为数据无法得到ID和最新时间对应的值在一句话中。
突然想起了ORACLE的分析函数。OK,使用
LAG
优化了原SQL语句。
执行成本从19106下降到10139，结果集返回的时间下降到15秒左右。响应时间有大幅提高。
总结：
此次优化的核心思想，减少SQL的重复执行，能在一句SQL中执行完成的，就不要放到两句或者是多个子查询中执行。
通过使用分析函数LAG，将分类和排序一次完成。一定要多多尝试使用分析函数，往往会给你带来非常的惊喜。
看看他的问题，比较麻烦些。
select sbl.fee_terminal_id , sbl.status, smr.status, smr.report_time
from XXX_log sbl, XXX_report smr
where sbl.task_id in ('8888','9999','1111','2222')
and sbl.msg_id=smr.msg_id(+)
and sbl.submit_time>= '20080927'
and sbl.submit_time<= '20080929'
and smr.report_time>= '20080927'
and smr.report_time<= '20080929'
and (fee_terminal_id, report_time) in (
select distinct(sbl.fee_terminal_id), max(smr.report_time)
from XXX_log sbl, XXX_report smr
where sbl.task_id in ( '8888', '9999', '1111', '2222')
and sbl.msg_id=smr.msg_id(+)
and sbl.submit_time>= '20080927'
and sbl.submit_time<= '20080929'
and smr.report_time>= '20080927'
and smr.report_time<= '20080929'
group by fee_terminal_id
);
from XXX_log sbl, XXX_report smr
where sbl.task_id in ('8888','9999','1111','2222')
and sbl.msg_id=smr.msg_id(+)
and sbl.submit_time>= '20080927'
and sbl.submit_time<= '20080929'
and smr.report_time>= '20080927'
and smr.report_time<= '20080929'
and (fee_terminal_id, report_time) in (
select distinct(sbl.fee_terminal_id), max(smr.report_time)
from XXX_log sbl, XXX_report smr
where sbl.task_id in ( '8888', '9999', '1111', '2222')
and sbl.msg_id=smr.msg_id(+)
and sbl.submit_time>= '20080927'
and sbl.submit_time<= '20080929'
and smr.report_time>= '20080927'
and smr.report_time<= '20080929'
group by fee_terminal_id
);
他需要提取某个时间段内，某个ID(fee_terminal_id)最新时间的状态。其实如果是在一张表中，直接MAX(report_time)就可以了。但是，恰恰这是两张表。每张表中一个状态，通常的情况就是我们首先得到ID及其最大的（最新）报告时间，然后再“自连接”自己进行WHERE条件的查询。
上面的SQL就是通过这种基本的想法来实现的。
一般情况下，应该是没有问题的。但是，这次问题却出现了。主要就是数据太大，执行了30分钟没有看到有结果集返回。
首先因为两张基本表（XXX_log sbl, XXX_report smr）都是分区表，不然数据也不会这么大呀。进行必要的分区工作，要知道，再精良的SQL，数据太多也会有问题。优化的第一步就是“少做”（片面一点讲，性能真的是设计出来的）。
完成后，直接执行原始SQL，基本上180秒左右，可以看到结果集返回。
至此，都有些“跑题”，还有过滤重复呢（其实就是找出最新时间的ID状态即可）。
看着这句SQL我总是有些不死心，看看其实这句SQL的两个部分的SQL语句基本相同，其实一部分就可以得到所有信息，就是因为数据无法得到ID和最新时间对应的值在一句话中。
突然想起了ORACLE的分析函数。OK,使用
LAG
select a,b,c,d
from ( select sbl.fee_terminal_id a,
sbl.status b,
smr.status c,
smr.report_time d,
lag(sbl.fee_terminal_id,1,0) over(partition by sbl.fee_terminal_id order by smr.report_time desc) flag
from xxx_log sbl, xxx_report smr
where sbl.task_id in ( '8888', '9999', '1111', '2222')
and sbl.msg_id = smr.msg_id(+)
and sbl.submit_time >= '20080927'
and sbl.submit_time <= '20080929'
and smr.report_time >= '20080927'
and smr.report_time <= '20080929')
where flag = 0
from ( select sbl.fee_terminal_id a,
sbl.status b,
smr.status c,
smr.report_time d,
lag(sbl.fee_terminal_id,1,0) over(partition by sbl.fee_terminal_id order by smr.report_time desc) flag
from xxx_log sbl, xxx_report smr
where sbl.task_id in ( '8888', '9999', '1111', '2222')
and sbl.msg_id = smr.msg_id(+)
and sbl.submit_time >= '20080927'
and sbl.submit_time <= '20080929'
and smr.report_time >= '20080927'
and smr.report_time <= '20080929')
where flag = 0
优化了原SQL语句。
执行成本从19106下降到10139，结果集返回的时间下降到15秒左右。响应时间有大幅提高。
总结：
此次优化的核心思想，减少SQL的重复执行，能在一句SQL中执行完成的，就不要放到两句或者是多个子查询中执行。
通过使用分析函数LAG，将分类和排序一次完成。一定要多多尝试使用分析函数，往往会给你带来非常的惊喜。
BTW,国庆节快乐。-:)
本文转自Be the miracle!博客51CTO博客，原文链接http://blog.51cto.com/miracle/102792如需转载请自行联系原作者
Larry.Yue
