系统中有一个表积累了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 | <==选择「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;