oracle 中间表效率不高,oracle数据源的报表sql计算慢&解决

问题描述

项目里有些报表出来的速度特别慢，尽管对润乾报表和Oracle数据库做了很多优化，效果还是不理想，这些报表普遍数据量比较大，涉及到的数据库表多(几十张)、表间关联频繁(还有自连接)，报表里也有多个汇总、比值等计算。

以其中一个明细报表为例，它的SQL如下：

(select *

from (select syb.org_abbn as syb,

max(xmb.org_abbn) as xmb,

sub.org_subjection_id as sub_id,

oi.org_abbn as org_abb,

rm.rec_notice_org_id,

rm.synergic_team as xz_team,

xzdw.coding_name as xz_org,

l.requisition_cd as req_cd,

l.requisition_id as req_id,

l.note as req_note,

nvl(decode(l.ops_content6,

2000200012,

'否',

2000200011,

'是'),

'') as sflj,

--太长了，省略大部分select子句

fromlcr l

left join lcrrm on rm.requisition_id =

l.master_bill_id

andrm.table_type = '0'

andnvl(rm.bsflag, 0) != 1

left join cos sub on l.org_id = sub.org_id

andnvl(sub.bsflag, 0) != 1

left join coioi on oi.org_id = sub.org_id

andnvl(oi.bsflag, 0) != 1

--太长了，省略大部分jion

wherel.table_type = '1'

andl.requisition_state = '0101020304'

andnvl(l.bsflag, 0) != 1

andto_char(l.back_date, 'yyyy-MM-dd') between '2012-01-01' and

'2012-04-25'

group by l.requisition_id,

l.note,

l.requisition_type,

sub.org_subjection_id,

syb.org_abbreviation,

rm.rec_notice_org_id,

oi.org_abbreviation,

--太长了，省略大部分group by 字段

) a--主查询a

LEFT JOIN crviewve--视图ve

ON ve.requisition_id = a.req_id

这个sql里关联的表很多，嵌套多层子查询，最后又与一个视图进行关联(视图也很复杂)。该报表查询4个月的数据，计算时间为6分42秒，太慢了远远达不到用户要求。

我们对这个报表做过几次优化，但sql复杂度较高，基本没有优化空间，而且由于是实时查询，所以无法采用事先计算建立中间表的方法。后对这个报表进行监控发现，数据集SQL执行需要5分钟，计算展现需要1分多钟，数据集SQL慢的原因是其中两个子查询(主查询a和视图ve)做join的效率极低。

所以优化思路定为——优化数据集取数，改善SQL的join效率低的情况，顺便优化报表计算展现。

解决过程

我们是用集算器(报表厂家的东西)解决这个问题：

1、拆分原报表数据集SQL

分别把两个子查询sql写到集算器，并用switch完成关联(Switch是它的函数，比较新颖，能将外键指向的记录直接当成本记录的属性，也支持join，视不同情况使用)。

2、消除报表格间运算

将原报表模板中的比值和汇总值这些全部移到集算器中做，少了单元格遍历，报表计算速度也能提高。

3、将结果集一次返回给报表

完成所有数据准备后，把计算结果一次性返回给报表工具，报表接收到数据源后直接进行展现(不再做其他如格间计算类的影响效率的计算)。

总体代码如下：

解决效果

该报表展现时间从原来的6分42秒锐降到57秒，优化效果非常明显，超出了用户预期。其他有类似问题的报表也准备采用这个思路。

问题小结

主查询a和视图ve分别在Oracle跑时只需要10到40秒，但二者做jion却需要好几分钟，原因在于Oracle在完全自动制定查询计划的时候，并不是每次都能找到合理的方法(人工干预比较费劲)。集算器能提升性能是因为ve是a的维表，可以用特别的switch方法。由人来决定复杂查询的路径，结合Oracle的基础查询语句，速度就显著提升了。