一次涉及两个大表关联的优化

原创 2013年12月05日 15:29:56

sql:

select *
  from (select fd.analystid,
               dr.objid DOCID,
               dr.title,
               row_number() over(partition by fd.analystid order by dr.doctime desc) rown
          from fa_docanalystrela fd
         inner join doc_researchreportcore dr
            on (fd.docid = dr.objid )
            where dr.doctime > sysdate - 90)
   where rown = 1
   and  analystid in ('115880',
                     '84947',
                     '114456',
                     '83715',
                     '85335',
                     '84106',
                     '116090',
                     '112112',
                     '112265',
                     '127614');

执行计划:



执行很慢,要12s左右,而只得到7条记录。


这是一个看似很简单的sql,这个sql当时优化了一下午,想了很多方法,也没拿到好的优化方法,晚上请高手帮忙也没搞定。

第三天,没事的时候,好好分析了下这两个表的关联,找到了一点突破口:

(1)、查看量表的记录数:

select count(*) from DOC_RESEARCHREPORTCORE;--832391
select count(*) from FA_DOCANALYSTRELA--699367

(2)、查看量表存储空间大小:


(3)、根据表关系查看经过过滤条件后量表关联时的记录:

select count(*) from doc_researchreportcore where doctime > sysdate - 90

--73949

select count(*) from fa_docanalystrela where analystid in ('115880',
                     '84947',
                     '114456',
                     '83715',
                     '85335',
                     '84106',
                     '116090',
                     '112112',
                     '112265',
                     '127614');
    --2829

 分析:

1、由(1)和(2)可看到,量表记录差不过,但是存储空间相差很大,刚刚开始以为是

doc_researchreportcore数据块稀疏或者有过多的空块造成的,处理使块收缩后所占存储空间基本上没有变化;

查看表结构发现该表字段很多的缘故,这里想到把所需要搜索的字段建组合索引,这个消除对大表的回表。

这里建立组合索引(DOCTIME, OBJID, TITLE)。

2、由(3)看出,入股走loop关联的话会是2829和73949的嵌套循环,这个会使开销很大;这里考虑走hashjoin,让小表fa_docanalystrela去驱动大表doc_researchreportcore,这样会极大的减少开销。

下面是修改后的sql和执行计划:执行时间为0.998s,多次请缓存区执行时间都控制在1s以内,基本上满足了需求。

select *
  from (select fd.analystid,
               dr.objid DOCID,
               dr.title,
               row_number() over(partition by fd.analystid order by dr.doctime desc) rown
          from fa_docanalystrela fd
         inner join doc_researchreportcore dr
            on (fd.docid = dr.objid )
            where dr.doctime > sysdate - 90)
   where rown = 1
   and  analystid in ('115880',
                     '84947',
                     '114456',
                     '83715',
                     '85335',
                     '84106',
                     '116090',
                     '112112',
                     '112265',
                     '127614');


执行计划:

 

 

总结:

这里用到了分析表连接关系,走正确的表关联关系和减少回表。

这里执行时间有12s降到0.998s,虽然基本上满足的需求,但是有2189次递归、43次排序、2861次物理读和3368次逻辑读,这些是必须的开销。


20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

记得5年前遇到一个SQL,就是一个简单的两表关联,SQL跑了差不多一天一夜,这两个表都非常巨大,每个表都有几十个G,数据量每个表有20多亿,表的字段也特别多。 相信大家也知道SQL慢在哪里了,单个进...
  • robinson1988
  • robinson1988
  • 2016年02月27日 21:57
  • 13234

两个超大表做关联更新的优化 之 批量更新

http://blog.itpub.net/16628454/viewspace-1043225/ 两个超大表做关联更新的优化 2010-12-17 13:43:01 分类...
  • hackshi008
  • hackshi008
  • 2015年09月15日 14:55
  • 330

ORACLE多表查询优化

  • 2010年10月09日 12:18
  • 50KB
  • 下载

ORACLE+SQL性能优化(全)

  • 2016年05月11日 13:42
  • 2.32MB
  • 下载

20亿与20亿表关联优化方法(超级大表与超级大表join优化方法)

记得5年前遇到一个SQL,就是一个简单的两表关联,SQL跑了差不多一天一夜,这两个表都非常巨大,每个表都有几十个G,数据量每个表有20多亿,表的字段也特别多。 相信大家也知道SQL慢在哪里了,单个进...
  • robinson1988
  • robinson1988
  • 2016年02月27日 21:57
  • 13234

两个超大表做关联更新的优化 之 批量更新

http://blog.itpub.net/16628454/viewspace-1043225/ 两个超大表做关联更新的优化 2010-12-17 13:43:01 分类...
  • hackshi008
  • hackshi008
  • 2015年09月15日 14:55
  • 330

ORACLE多表查询优化

 转自某地,对作者很愧疚- -!不晓得地址了.. ORACLE多表查询优化这里提供的是执行性能的优化,而不是后台数据库优化器资料:参考数据库开发性能方面的各种问题,收集了一些优化方案统计如下(当然,象...
  • ty1921
  • ty1921
  • 2009年10月28日 17:39
  • 4723

分享ORACLE中大数据量查询实现优化

大数据量查询,对数据库开发者来说,性能问题往往是最需要费尽心机的,借此总结自己优化此类问题的心得与大家分享,以免大家走更多的弯路。1.使用主键临时表   大数据量表关联查询,是性能开销的主要原因。通过...
  • wga168
  • wga168
  • 2010年07月02日 20:53
  • 5019

Oracle 两表关联更新

有TA, TB两表,假设均有三个栏位id, name, remark. 现在需要把TB表的name, remark两个栏位通过id关联,更新到TA表的对应栏位。 建表脚本: [sql] v...
  • zcouy
  • zcouy
  • 2017年01月10日 14:50
  • 1093

Oracle 查询技巧与优化(二) 多表查询

关于Oracle多表查询的一些技巧和优化~
  • wlwlwlwl015
  • wlwlwlwl015
  • 2016年08月05日 08:59
  • 6213
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:一次涉及两个大表关联的优化
举报原因:
原因补充:

(最多只允许输入30个字)