search
HomeDatabaseMysql TutorialORACLE 报表数据库开发设想
ORACLE 报表数据库开发设想Jun 07, 2016 pm 03:50 PM
oracledevelopReportdatabaseshark

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 索引

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

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

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
什么是oracle asm什么是oracle asmApr 18, 2022 pm 04:16 PM

oracle asm指的是“自动存储管理”,是一种卷管理器,可自动管理磁盘组并提供有效的数据冗余功能;它是做为单独的Oracle实例实施和部署。asm的优势:1、配置简单、可最大化推动数据库合并的存储资源利用;2、支持BIGFILE文件等。

oracle怎么查询所有索引oracle怎么查询所有索引May 13, 2022 pm 05:23 PM

方法:1、利用“select*from user_indexes where table_name=表名”语句查询表中索引;2、利用“select*from all_indexes where table_name=表名”语句查询所有索引。

Oracle怎么查询端口号Oracle怎么查询端口号May 13, 2022 am 10:10 AM

在Oracle中,可利用lsnrctl命令查询端口号,该命令是Oracle的监听命令;在启动、关闭或重启oracle监听器之前可使用该命令检查oracle监听器的状态,语法为“lsnrctl status”,结果PORT后的内容就是端口号。

oracle全角怎么转半角oracle全角怎么转半角May 13, 2022 pm 03:21 PM

在oracle中,可以利用“TO_SINGLE_BYTE(String)”将全角转换为半角;“TO_SINGLE_BYTE”函数可以将参数中所有多字节字符都替换为等价的单字节字符,只有当数据库字符集同时包含多字节和单字节字符的时候有效。

oracle怎么删除sequenceoracle怎么删除sequenceMay 13, 2022 pm 03:35 PM

在oracle中,可以利用“drop sequence sequence名”来删除sequence;sequence是自动增加数字序列的意思,也就是序列号,序列号自动增加不能重置,因此需要利用drop sequence语句来删除序列。

oracle怎么查询数据类型oracle怎么查询数据类型May 13, 2022 pm 04:19 PM

在oracle中,可以利用“select ... From all_tab_columns where table_name=upper('表名') AND owner=upper('数据库登录用户名');”语句查询数据库表的数据类型。

oracle查询怎么不区分大小写oracle查询怎么不区分大小写May 10, 2022 pm 05:45 PM

方法:1、利用“LOWER(字段值)”将字段转为小写,或者利用“UPPER(字段值)”将字段转为大写;2、利用“REGEXP_LIKE(字符串,正则表达式,'i')”,当参数设置为“i”时,说明进行匹配不区分大小写。

Oracle怎么修改sessionOracle怎么修改sessionMay 13, 2022 pm 05:06 PM

方法:1、利用“alter system set sessions=修改后的数值 scope=spfile”语句修改session参数;2、修改参数之后利用“shutdown immediate – startup”语句重启服务器即可生效。

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment