ORACLE报表数据库开发设想

OLAP 称为在线分析,其实就是报表系统,和BI系统. BI系统是套产品在这里不谈. 分析和报表其实都是用存储过程开发出来的,一个是在线提供给用户使用,另一个是离线提供给同事使用的. 在线分析目前来看应用不广,所涉及到的数据量相对比较小,只是用户量比较大 1 用

OLAP 称为在线分析,其实就是报表系统,和BI系统. BI系统是套产品在这里不谈. 分析和报表其实都是用存储过程开发出来的,一个是在线提供给用户使用,另一个是离线提供给同事使用的.

在线分析目前来看应用不广,所涉及到的数据量相对比较小,只是用户量比较大

1 用户只关心自己的. 比如购买次数,购买总额,等用户所关心的数据

2 产品关联,比如说购买该产品的用户还购买了其他什么产品!

3 产品火红度;

而报表涉及到所有的数据,包含历性数据. 每个部门有不同的报表要求,每个同事,每个部门领导都会提些自己关心的报表.

ORACLE 数据库 是从交易型数据库发展过来的,处理分析型数据时候总有点力不从心!

1 开始安装数据库时候选择OLAP 它会自动调整下必要的参数

2 设置64-128KB的数据块 而不是默认的8KB

3 分层设计, 因为报表众多,如果直接从原始表获取必然造成性能大阻塞. 因此要把基础的,共同的做成数据表,其他报表直接从这些基数表里获取数据. 这样就极大减少了数量.

a 抽取源表层 b 基础表层 C 共同层 D 部门层

如何分? 哪些数据做在哪里,是需要多业务了解和熟悉,对公司和各个部门的报表了解,方能有大概的想法, 这些不一定一开始就能搞定的,需要不断地优化中.因为短时间内无法对业务的彻底熟悉.

4 任务调度:

采用储存过程和软件包来做每个报表,每个表的数据产生. 那么这些任务之间必然产生了依赖.

采用ORACLE 本身的JOB来调度,采用存储过程里面包含存储过程,也就是说JOB调度启动存储过程,启动存储过程把相关的存储过程包含在一起.

该方法不太灵活,扩展性比较差,维护比较难!

应该采用crontab 方式的调度. 比如说写个轮休的JOB 该JOB每隔5-10分钟运行一次. 该JOB只调用一个存储过程. 存储过程启动任务,任务是软件包或者是存储过程.

该存储过程 读取任务信息表, 任务依赖表,何时启动该任务, 并监督任务运行状况和报警.

5 软件包里 一般包含 a 抽取存储过程; b 清单存储过程;c 日数据存储过程; d 周数据存储过程; e 月存储过程;f 移动到结果表的存储过程;g 回滚的存储过程;h清理过期数据的过程

a 抽取存储过程 把源表的数据抽取到临时表中,这里指任务所需数据的表; 这里的临时表是物理的 以_TMP命名的.

之所以采用临时表法,因为ORACLE 对表连接成本很高, 尤其是多表的LEFT JOIN +LEFT JOIN . 采用临时表可以把必要的字段,必要的行形成较小的数据块.

b 清单存储过程

清单的意思是 这部分数据要临时存上1-3个月,主要的是去重的要求, 求一个月的人数不能从每天的人数SUM过来. 以_LST命名 这个清单要做成分区表 月,日或者小时的分区.

C 日数据过程 是从清单里获取数据进行统计,当然如果没有清单直接从抽取的临时表中获得

D 周过程, 周这个时间很麻烦的事情 尤其涉及到跨年的周. 如果不去重可以直接从日数据中提取

E 月过程 同上.

F 过程: 是避免结果表的更新影响到领导的查询, 所以先把所有的数据整合在一个临时汇总表中,再移动到结果表

G过程:是个重要的过程,它主要功能是实现回滚UNDO操作,因为依靠ORACLE自身的UNDO机制是很慢的.

处理月报表每天都累加一次的情况,或者是清单过于庞大,保留一个月太多了,或者说扫描一个月的数据太久了.那么采取每天跑一次,每天加一次.

类似是 update table set value=value+new_value;

这样的场景,如果运算过程中发生了故障,就会发生前后数据不一致,只更新了30%的数据就故障了. 所以更新前,把新的值存储在回滚表中.每次运行前调用回滚过程,检查回滚标志

如果非正常结束,那么提取相应的数据 对数据进行 UPDATE TABLE SET VALUE=VALUE-NEW_VALUE 操作;

H 清理过程: 这里主要是清理暂时保留一段时间的清单表.

每个过程运行前 都要做 TRUNCATE TABLE XXXXX_TMP 的清空表的操作. 如果涉及到清单和目的表,那么要DELETE TABLE WHERE YYYY= XXXX 因为避免得到重复的数据.

6 游标批处理

因为数据量很大成百上千万行, 不可能一次性地提交上去. 比如 insert into table_name (xx,yyy,zz,hhh,) select xx,yy,zz,hh from table_tmp left jion table_tmp2; 会很慢滴

采用游标和批提取方式

cursor cur_day_result is --计算月登录人数和次数

select provcode from table_b group by 1;

type type_provcode is table of oss_openplat_truslogin_day_lst.provcode%type index by binary_integer;

l_ary_provcode type_provcode;

begin

open cur_day_result;
loop
fetch cur_day_result bulk collect into

l_ary_provcode

limit g_batch_size_n; --- 这里可以控制提取行数

forall i in 1..l_ary_provcode.count
insert into login_day_lst
( provcode)

values(l_ary_provcode )

commit; -- 这里把一部分数据提交到数据上

end loop

7 复杂的要求:

经常有 连续三个月的购买用户人数, 日增加额和增加率, 当天与上个月当天的比 即同比; 月累加值.

采用MERG INTO和 UPDATE 的方式会比较慢. 直接采用INSERT 和DELETE

比如 日期, 分类1,分类2,分类3,统计值,统计值月累加;

通过 日数据过程和月数据过程 分别生成了数据

日期, 分类1,分类2,分类3,统计值;

日期, 分类1,分类2,分类3,统计值月累加;

分别insert into 到 汇总表 (日期, 分类1,分类2,分类3,统计值,统计值月累加)

insert into 汇总表 (日期, 分类1,分类2,分类3,统计值,统计值月累加) select 日期, 分类1,分类2,分类3,统计值,0 from table_day_tmp;

把不属自己的字段值0

最后 汇总表在移动结果表时

select 日期, 分类1,分类2,分类3, sum(统计值),sum(统计值月累加) from 汇总表 group by 日期, 分类1,分类2,分类3

8 宽表 行转列

思想是 通过增加列的数量来减少行的数量. 比如解决 连续三个月的购买用户人数 的报表需求

我们有 用户表,用户购买记录表; 如果我们的用户相对比较少 有1百万吧 如果这1百万人中 12个月购买记录行数达到2亿行.平均每个月有1千6百万行;

从3个月的记录中大约5千4百万统计连续3个月的用户,应该会比较慢的.

假如做个宽表 用户 1月购买次数,2月购买次数.......12月购买次数, 第一次购买时间,最后次购买时间

那么这个表只有1百行的记录

select 用户

from table

where 1月购买次数 > 0 and 2月购买次数>0 and 3月购买次数>0

9 报表分等级

如果说 所有的报表要在早上上班9前跑出来,这是个比较难以完成的任务. 在数据量非常少的情况下 比如20G 用 1台机器 32G内存 8个CPU 多个硬盘的RAID

确实可以达到要求. 如果数据量达到500GB级以上 就会出现麻烦事了.

因此 觉得要把报表分级别 实现优先级处理

A 级报表 在9:00前跑出 这一般都是公司业务核心报表 高层和老板 CTO CEO 这类人要看的

B级报表 在中午12:00前跑出 这个各部门领导关心的

C级报表 在下午下班6:00前 这个就是普通员工

D级报表 在晚上跑出来的; 比如监控之类的

10 RAC集群

RAC并不能提升性能 使用RAC关键是把任务分在不同节点上

A节点做主要的管理节点;

B节点做数据抽取同步节点,一当数据大的话必须24小时全天候时时抽取,时时同步;

C节点报表节点 ; 主要跑各个报表的任务过程

D节点页面节点 报表如果以HTML方式展现来,那么页面服务器访问的数据库必须单独的节点,避免其他操作影响到该节点.

E节点随机查询节点: 这个节点基本上做自己人查询数据,核对数据,更改数据的节点.

A 节点是RAC的管理节点 负责整个集群块的管理和锁的处理. 所以为了不影响性能必须单独用一个节点来负责整个集群的通讯

B 节点 要做24小时数据插入工作 也要单独使用一个

C 节点 重量级节点 该节点使用的机器比其他节点性能高出数倍. 内存达要更大 才能内存进行大量数据块的操作,而不是被LINUX交换分区掉了

D节点 面子节点 领导老板同事 访问页面的快慢体验就在这个节点上,如果跟其他节点合并在一起,容易被其他节点的任务把内存给占了.

7 分区表

一般分区达到2层 就是双分区.当有的情况下要达3层 物理月表 月表下日分区 日分区下是LIST分区. 物理月表 是人工给表起名字 "TABLE_201206 "

这样要不断地人工建新表, 而存储过程访问时候需要从数据字典里获得该表名, 要不采用时间拼接法 然后采用动态语句.编写起来比较繁琐.

分区表 ORACLE建议 大于2G的表进行分区. 那么最小的分区应该是容量多大? 这要涉及到机器性能和IO吞吐量,以及一个分区全表扫描时间的忍受程度.

如果分区1个G 而全扫一次要10分钟,那么自然不可接受. 那么一个分区应该在1分钟内完成全扫描

11 索引

基本上不建议在表里建索引,采用多层分区表,实现全表扫描. 因为索引会导致反而比全扫描慢,索引在大规模数据更新的时候维护成本高. 会极大影响各个报表的运行时间.

索引大部分用在结果表上,因为结果表插入的数据量最少,更新的频率最低,维护成本最小.查询效率最高.