APEX的IR下大量数据检索的提速方法---兼谈动态SQL在IR的实现

系统中有一个表积累了10多年的数据，记录有上百万条，系统的所有部门还不让将旧数据Archive出去。原因是有时候要检索旧数据（1回/月的频度）。

但是，日常检索集中在今年及去年的数据，结果返回要花10秒以上。为此，终端用户不满意。

 

解决这一问题的办法可能是：按一定的Key分割数据，然后用动态SQL将需要的部分表示出来，以避免费时的大量数据搜索。

 

APEX下动态SQL可以用到一般的Report上，但是对于功能较强的InteractivReport（IR），到目前为止，还不支持动态SQL。

开发组的Joel在2010/12曾说过，当REF CURSOR在Oracle11.1.06获得全面支持之后，他们会在一个主要APEX版本，比如4.1.1上实现动态SQL的完全支持。

https://forums.oracle.com/forums/thread.jspa?messageID=9204564&tstart=0#9204564

可是，目前的4.1.1版本并没有支持动态SQL。我们也许要等到APEX5.0？

 

其实，关于APEX下实现动态SQL已经有一些好的Solution。

这里介绍2个。

 

其一，使用APEX_Collection

详情可参考： http://www.oracleapplicationexpress.com/tutorials/71

这个方法比较简单而且通用性也好。通过APEX提供的Collection功能，使用一个函数可以得到符合自己条件的动态数据集（Collection）。

因为Collection在一个Session有效，每次打开页面时都要创建Collection，离开时又要删除。（当然，通过PL/SQL可以控制何时创建）

此外，对于比较大的数据集创建需要时间，所以页面显示的总时间并未减少。

还有， Collection最多可收集50个域的数据，当显示数据大于50个域就无法对应了。

 

其二，使用PIPELINED FUNCTIONS

详情可参考：http://sungur.wordpress.com/2009/10/11/apex-interactive-reports-with-dynamic-sql/

这个方法稍微复杂一些，因为要定义复杂的数据结构。有些懒人，比如我，就不大愿意用。

 

具体使用哪种方法要根据自己的数据，用户的需求定。没有哪个绝对好。

 

我的方法

我决定把数据按年度做成几个Snapshot。

然后将snapshot结合（Union All）起来使用。

 

事前准备

做好数据分割。

为啥不做表？因为表可以被修改，万一被修改，会出现数据不整合的问题。

但Snapshot缺省是Readonly。 

 

 往年的数据30天更新一次。（用的是最笨的Complete方式。这样snapshot维护简单些）

create snapshot AT_bc_v1_2002
refresh complete next sysdate+30
as select * from AT_bc_v1 where BCASE_APR_DATE like '2002%';

......

今年的数据5分钟更新一次。

create snapshot AT_bc_v1_2012
refresh complete next sysdate+(1/24/60)*5
as select * from AT_bc_v1 where BCASE_APR_DATE like '2012%';

 

注意：对大量数据以Complete方式更新snapshot，而且以5分钟频度，可能会挨骂的。 Why？做之前，先问问你的DAB。 大部分生产数据库（Production Database）都会使用Archivelog方式运行。 Snapshot的更新在10g之后，不用Truncate，改用Delete删除旧数据，所以会产生更多的Archive Log。 以5分钟频度更新大量数据，很快ArchiveLog领域会用光，数据库就宕机了。 （这也是我做多个Snapshot的原因。） DBA会很不高兴，你公司的用户，你的老板都会很不高兴。搞不好，丢了饭碗也有可能。 三思而后行这句话是蛮有道理的。

 

IR中原来使用的SQL

select t.*  from AT_BC_V1 t
where BCASE_APR_DATE like :P25_YEAR||'%'

（IR页面上有一个Multi-selection框，名为P25_YEAR，选择年度后，SQL根据这个数值可找到对应的数据。）

YEAR	200220032004 200520062007 200820092010 20112012 -ALL-<==选择「All」时检索很花时间。

 

改良后的SQL

select * from AT_BC_V1_this_year where :P25_YEAR=2012
Union All
select * from AT_BC_V1_2011 where :P25_YEAR=2011
Union All
...............

Union All
select * from AT_BC_V1_2002 where :P25_YEAR=2002
Union All
select * from AT_BC_V1      where :P25_YEAR=20
;

 

（有人说这是动态选择表的一种办法。因为OracleSQL里有Case语句，可以用到选择域和条件中，就是不能用来选择表。）

 

效果如何？

在测试机通过2万多条数据的subset测试，可以看到速度可提高2-3倍。

 新旧方法个年度数据表示所用时间（单位：秒）

Year	Records	New  SQL	Old SQL	Old/New Ratio
2012	2,032	0.51	1.43	2.8
2011	2,451	0.46	1.55	3.4
2010	2,267	0.34	1.24	3.6
2009	2,403	0.47	1.25	2.7
2008	2,686	0.47	1.27	2.7
2007	3,024	0.33	1.27	3.8
ALL	23,687	2.92	2.91	1.0

 

大家都知道，随着数据积累，数据库的反应会慢慢恶化。

按年度分割后，在大部分条件下，可以抑制慢性恶化。

但是，与此而来的就是要定期（比如，每年）维护程序。

还有，数据双重保存占据空间（好在硬盘便宜了）。

 

这种提速效果的真正原因何在？ 其实，在我这里，还不是数据分割。真正的原因是原来的查询对象是个复杂的View，这个View的执行很费时间。把View的结果写到Snapshot里，查询时节省了对View的复杂计算，所以快了不少。 如果你也因为View引起查询速度变慢，你大可不必做这么多分割。做一个Snapshot也许就能解决问题。 但是，是否可行，Snapshot的更新频度，要和DBA事先商量。

 

这个办法不是到处都适应的。还是那句话，具体问题具体解决。

 

 

追记

手动更新Snapshot时：

begin
dbms_snapshot.refresh('at_bc_v1_this_year','C');
end;

删除时：
drop snapshot at_bc_v1_this_year;