ORACLE報(bào)表數(shù)據(jù)庫開發(fā)設(shè)想

OLAP 稱為在線分析,其實(shí)就是報(bào)表系統(tǒng),和BI系統(tǒng). BI系統(tǒng)是套產(chǎn)品在這里不談. 分析和報(bào)表其實(shí)都是用存儲(chǔ)過程開發(fā)出來的,一個(gè)是在線提供給用戶使用,另一個(gè)是離線提供給同事使用的. 在線分析目前來看應(yīng)用不廣,所涉及到的數(shù)據(jù)量相對(duì)比較小,只是用戶量比較大 1 用

OLAP 稱為在線分析,其實(shí)就是報(bào)表系統(tǒng),和BI系統(tǒng). BI系統(tǒng)是套產(chǎn)品在這里不談. 分析和報(bào)表其實(shí)都是用存儲(chǔ)過程開發(fā)出來的,一個(gè)是在線提供給用戶使用,另一個(gè)是離線提供給同事使用的.

在線分析目前來看應(yīng)用不廣,所涉及到的數(shù)據(jù)量相對(duì)比較小,只是用戶量比較大

1 用戶只關(guān)心自己的. 比如購買次數(shù),購買總額,等用戶所關(guān)心的數(shù)據(jù)

2 產(chǎn)品關(guān)聯(lián),比如說購買該產(chǎn)品的用戶還購買了其他什么產(chǎn)品!

3 產(chǎn)品火紅度;

而報(bào)表涉及到所有的數(shù)據(jù),包含歷性數(shù)據(jù). 每個(gè)部門有不同的報(bào)表要求,每個(gè)同事,每個(gè)部門領(lǐng)導(dǎo)都會(huì)提些自己關(guān)心的報(bào)表.

ORACLE 數(shù)據(jù)庫 是從交易型數(shù)據(jù)庫發(fā)展過來的,處理分析型數(shù)據(jù)時(shí)候總有點(diǎn)力不從心!

1 開始安裝數(shù)據(jù)庫時(shí)候選擇OLAP 它會(huì)自動(dòng)調(diào)整下必要的參數(shù)

2 設(shè)置64-128KB的數(shù)據(jù)塊 而不是默認(rèn)的8KB

3 分層設(shè)計(jì), 因?yàn)閳?bào)表眾多,如果直接從原始表獲取必然造成性能大阻塞. 因此要把基礎(chǔ)的,共同的做成數(shù)據(jù)表,其他報(bào)表直接從這些基數(shù)表里獲取數(shù)據(jù). 這樣就極大減少了數(shù)量.

a 抽取源表層 b 基礎(chǔ)表層 C 共同層 D 部門層

如何分? 哪些數(shù)據(jù)做在哪里,是需要多業(yè)務(wù)了解和熟悉,對(duì)公司和各個(gè)部門的報(bào)表了解,方能有大概的想法, 這些不一定一開始就能搞定的,需要不斷地優(yōu)化中.因?yàn)槎虝r(shí)間內(nèi)無法對(duì)業(yè)務(wù)的徹底熟悉.

4 任務(wù)調(diào)度:

采用儲(chǔ)存過程和軟件包來做每個(gè)報(bào)表,每個(gè)表的數(shù)據(jù)產(chǎn)生. 那么這些任務(wù)之間必然產(chǎn)生了依賴.

采用ORACLE 本身的JOB來調(diào)度,采用存儲(chǔ)過程里面包含存儲(chǔ)過程,也就是說JOB調(diào)度啟動(dòng)存儲(chǔ)過程,啟動(dòng)存儲(chǔ)過程把相關(guān)的存儲(chǔ)過程包含在一起.

該方法不太靈活,擴(kuò)展性比較差,維護(hù)比較難!

應(yīng)該采用crontab 方式的調(diào)度. 比如說寫個(gè)輪休的JOB 該JOB每隔5-10分鐘運(yùn)行一次. 該JOB只調(diào)用一個(gè)存儲(chǔ)過程. 存儲(chǔ)過程啟動(dòng)任務(wù),任務(wù)是軟件包或者是存儲(chǔ)過程.

該存儲(chǔ)過程 讀取任務(wù)信息表, 任務(wù)依賴表,何時(shí)啟動(dòng)該任務(wù), 并監(jiān)督任務(wù)運(yùn)行狀況和報(bào)警.

5 軟件包里 一般包含 a 抽取存儲(chǔ)過程; b 清單存儲(chǔ)過程;c 日數(shù)據(jù)存儲(chǔ)過程; d 周數(shù)據(jù)存儲(chǔ)過程; e 月存儲(chǔ)過程;f 移動(dòng)到結(jié)果表的存儲(chǔ)過程;g 回滾的存儲(chǔ)過程;h清理過期數(shù)據(jù)的過程

a 抽取存儲(chǔ)過程 把源表的數(shù)據(jù)抽取到臨時(shí)表中,這里指任務(wù)所需數(shù)據(jù)的表; 這里的臨時(shí)表是物理的 以_TMP命名的.

之所以采用臨時(shí)表法,因?yàn)镺RACLE 對(duì)表連接成本很高, 尤其是多表的LEFT JOIN +LEFT JOIN . 采用臨時(shí)表可以把必要的字段,必要的行形成較小的數(shù)據(jù)塊.

b 清單存儲(chǔ)過程

清單的意思是 這部分?jǐn)?shù)據(jù)要臨時(shí)存上1-3個(gè)月,主要的是去重的要求, 求一個(gè)月的人數(shù)不能從每天的人數(shù)SUM過來. 以_LST命名 這個(gè)清單要做成分區(qū)表 月,日或者小時(shí)的分區(qū).

C 日數(shù)據(jù)過程 是從清單里獲取數(shù)據(jù)進(jìn)行統(tǒng)計(jì),當(dāng)然如果沒有清單直接從抽取的臨時(shí)表中獲得

D 周過程, 周這個(gè)時(shí)間很麻煩的事情 尤其涉及到跨年的周. 如果不去重可以直接從日數(shù)據(jù)中提取

E 月過程 同上.

F 過程: 是避免結(jié)果表的更新影響到領(lǐng)導(dǎo)的查詢, 所以先把所有的數(shù)據(jù)整合在一個(gè)臨時(shí)匯總表中,再移動(dòng)到結(jié)果表

G過程:是個(gè)重要的過程,它主要功能是實(shí)現(xiàn)回滾UNDO操作,因?yàn)橐揽縊RACLE自身的UNDO機(jī)制是很慢的.

處理月報(bào)表每天都累加一次的情況,或者是清單過于龐大,保留一個(gè)月太多了,或者說掃描一個(gè)月的數(shù)據(jù)太久了.那么采取每天跑一次,每天加一次.

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

這樣的場(chǎng)景,如果運(yùn)算過程中發(fā)生了故障,就會(huì)發(fā)生前后數(shù)據(jù)不一致,只更新了30%的數(shù)據(jù)就故障了. 所以更新前,把新的值存儲(chǔ)在回滾表中.每次運(yùn)行前調(diào)用回滾過程,檢查回滾標(biāo)志

如果非正常結(jié)束,那么提取相應(yīng)的數(shù)據(jù) 對(duì)數(shù)據(jù)進(jìn)行 UPDATE TABLE SET VALUE=VALUE-NEW_VALUE 操作;

H 清理過程: 這里主要是清理暫時(shí)保留一段時(shí)間的清單表.

每個(gè)過程運(yùn)行前 都要做 TRUNCATE TABLE XXXXX_TMP 的清空表的操作. 如果涉及到清單和目的表,那么要DELETE TABLE WHERE YYYY= XXXX 因?yàn)楸苊獾玫街貜?fù)的數(shù)據(jù).

6 游標(biāo)批處理

因?yàn)閿?shù)據(jù)量很大成百上千萬行, 不可能一次性地提交上去. 比如 insert into table_name (xx,yyy,zz,hhh,) select xx,yy,zz,hh from table_tmp left jion table_tmp2; 會(huì)很慢滴

采用游標(biāo)和批提取方式

cursor cur_day_result is --計(jì)算月登錄人數(shù)和次數(shù)

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; --- 這里可以控制提取行數(shù)

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

values(l_ary_provcode )

commit; -- 這里把一部分?jǐn)?shù)據(jù)提交到數(shù)據(jù)上

end loop

7 復(fù)雜的要求:

經(jīng)常有 連續(xù)三個(gè)月的購買用戶人數(shù), 日增加額和增加率, 當(dāng)天與上個(gè)月當(dāng)天的比 即同比; 月累加值.

采用MERG INTO和 UPDATE 的方式會(huì)比較慢. 直接采用INSERT 和DELETE

比如 日期, 分類1,分類2,分類3,統(tǒng)計(jì)值,統(tǒng)計(jì)值月累加;

通過 日數(shù)據(jù)過程和月數(shù)據(jù)過程 分別生成了數(shù)據(jù)

日期, 分類1,分類2,分類3,統(tǒng)計(jì)值;

日期, 分類1,分類2,分類3,統(tǒng)計(jì)值月累加;

分別insert into 到 匯總表 (日期, 分類1,分類2,分類3,統(tǒng)計(jì)值,統(tǒng)計(jì)值月累加)

insert into 匯總表 (日期, 分類1,分類2,分類3,統(tǒng)計(jì)值,統(tǒng)計(jì)值月累加) select 日期, 分類1,分類2,分類3,統(tǒng)計(jì)值,0 from table_day_tmp;

把不屬自己的字段值0

最后 匯總表在移動(dòng)結(jié)果表時(shí)

select 日期, 分類1,分類2,分類3, sum(統(tǒng)計(jì)值),sum(統(tǒng)計(jì)值月累加) from 匯總表 group by 日期, 分類1,分類2,分類3

8 寬表 行轉(zhuǎn)列

思想是 通過增加列的數(shù)量來減少行的數(shù)量. 比如解決 連續(xù)三個(gè)月的購買用戶人數(shù) 的報(bào)表需求

我們有 用戶表,用戶購買記錄表; 如果我們的用戶相對(duì)比較少 有1百萬吧 如果這1百萬人中 12個(gè)月購買記錄行數(shù)達(dá)到2億行.平均每個(gè)月有1千6百萬行;

從3個(gè)月的記錄中大約5千4百萬統(tǒng)計(jì)連續(xù)3個(gè)月的用戶,應(yīng)該會(huì)比較慢的.

假如做個(gè)寬表 用戶 1月購買次數(shù),2月購買次數(shù).......12月購買次數(shù), 第一次購買時(shí)間,最后次購買時(shí)間

那么這個(gè)表只有1百行的記錄

select 用戶

from table

where 1月購買次數(shù) > 0 and 2月購買次數(shù)>0 and 3月購買次數(shù)>0

9 報(bào)表分等級(jí)

如果說 所有的報(bào)表要在早上上班9前跑出來,這是個(gè)比較難以完成的任務(wù). 在數(shù)據(jù)量非常少的情況下 比如20G 用 1臺(tái)機(jī)器 32G內(nèi)存 8個(gè)CPU 多個(gè)硬盤的RAID

確實(shí)可以達(dá)到要求. 如果數(shù)據(jù)量達(dá)到500GB級(jí)以上 就會(huì)出現(xiàn)麻煩事了.

因此 覺得要把報(bào)表分級(jí)別 實(shí)現(xiàn)優(yōu)先級(jí)處理

A 級(jí)報(bào)表 在9:00前跑出 這一般都是公司業(yè)務(wù)核心報(bào)表 高層和老板 CTO CEO 這類人要看的

B級(jí)報(bào)表 在中午12:00前跑出 這個(gè)各部門領(lǐng)導(dǎo)關(guān)心的

C級(jí)報(bào)表 在下午下班6:00前 這個(gè)就是普通員工

D級(jí)報(bào)表 在晚上跑出來的; 比如監(jiān)控之類的

10 RAC集群

RAC并不能提升性能 使用RAC關(guān)鍵是把任務(wù)分在不同節(jié)點(diǎn)上

A節(jié)點(diǎn)做主要的管理節(jié)點(diǎn);

B節(jié)點(diǎn)做數(shù)據(jù)抽取同步節(jié)點(diǎn),一當(dāng)數(shù)據(jù)大的話必須24小時(shí)全天候時(shí)時(shí)抽取,時(shí)時(shí)同步;

C節(jié)點(diǎn)報(bào)表節(jié)點(diǎn) ; 主要跑各個(gè)報(bào)表的任務(wù)過程

D節(jié)點(diǎn)頁面節(jié)點(diǎn) 報(bào)表如果以HTML方式展現(xiàn)來,那么頁面服務(wù)器訪問的數(shù)據(jù)庫必須單獨(dú)的節(jié)點(diǎn),避免其他操作影響到該節(jié)點(diǎn).

E節(jié)點(diǎn)隨機(jī)查詢節(jié)點(diǎn): 這個(gè)節(jié)點(diǎn)基本上做自己人查詢數(shù)據(jù),核對(duì)數(shù)據(jù),更改數(shù)據(jù)的節(jié)點(diǎn).

A 節(jié)點(diǎn)是RAC的管理節(jié)點(diǎn) 負(fù)責(zé)整個(gè)集群塊的管理和鎖的處理. 所以為了不影響性能必須單獨(dú)用一個(gè)節(jié)點(diǎn)來負(fù)責(zé)整個(gè)集群的通訊

B 節(jié)點(diǎn) 要做24小時(shí)數(shù)據(jù)插入工作 也要單獨(dú)使用一個(gè)

C 節(jié)點(diǎn) 重量級(jí)節(jié)點(diǎn) 該節(jié)點(diǎn)使用的機(jī)器比其他節(jié)點(diǎn)性能高出數(shù)倍. 內(nèi)存達(dá)要更大 才能內(nèi)存進(jìn)行大量數(shù)據(jù)塊的操作,而不是被LINUX交換分區(qū)掉了

D節(jié)點(diǎn) 面子節(jié)點(diǎn) 領(lǐng)導(dǎo)老板同事 訪問頁面的快慢體驗(yàn)就在這個(gè)節(jié)點(diǎn)上,如果跟其他節(jié)點(diǎn)合并在一起,容易被其他節(jié)點(diǎn)的任務(wù)把內(nèi)存給占了.

7 分區(qū)表

一般分區(qū)達(dá)到2層 就是雙分區(qū).當(dāng)有的情況下要達(dá)3層 物理月表 月表下日分區(qū) 日分區(qū)下是LIST分區(qū). 物理月表 是人工給表起名字 "TABLE_201206 "

這樣要不斷地人工建新表, 而存儲(chǔ)過程訪問時(shí)候需要從數(shù)據(jù)字典里獲得該表名, 要不采用時(shí)間拼接法 然后采用動(dòng)態(tài)語句.編寫起來比較繁瑣.

分區(qū)表 ORACLE建議 大于2G的表進(jìn)行分區(qū). 那么最小的分區(qū)應(yīng)該是容量多大? 這要涉及到機(jī)器性能和IO吞吐量,以及一個(gè)分區(qū)全表掃描時(shí)間的忍受程度.

如果分區(qū)1個(gè)G 而全掃一次要10分鐘,那么自然不可接受. 那么一個(gè)分區(qū)應(yīng)該在1分鐘內(nèi)完成全掃描

11 索引

基本上不建議在表里建索引,采用多層分區(qū)表,實(shí)現(xiàn)全表掃描. 因?yàn)樗饕龝?huì)導(dǎo)致反而比全掃描慢,索引在大規(guī)模數(shù)據(jù)更新的時(shí)候維護(hù)成本高. 會(huì)極大影響各個(gè)報(bào)表的運(yùn)行時(shí)間.

索引大部分用在結(jié)果表上,因?yàn)榻Y(jié)果表插入的數(shù)據(jù)量最少,更新的頻率最低,維護(hù)成本最小.查詢效率最高.

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com