Chinaunix首页 | 论坛 | 博客
  • 博客访问: 679485
  • 博文数量: 289
  • 博客积分: 3124
  • 博客等级: 中校
  • 技术积分: 5206
  • 用 户 组: 普通用户
  • 注册时间: 2008-10-24 23:42
个人简介

Bomi

文章存档

2015年(1)

2013年(1)

2012年(284)

2008年(3)

分类: 数据库开发技术

2012-02-05 10:23:57

研发的同事曾经遇到1个问题,看看他的问题,比较麻烦些。
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
     );
     他需要提取某个时间段内,某个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_idorder 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,将分类和排序一次完成。一定要多多尝试使用分析函数,往往会给你带来非常的惊喜。

阅读(301) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册