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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

該存儲過程 讀取任務(wù)信息表, 任務(wù)依賴表,何時啟動該任務(wù), 并監(jiān)督任務(wù)運行狀況和報警.

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

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

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

b 清單存儲過程

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

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

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

E 月過程 同上.

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

G過程:是個重要的過程,它主要功能是實現(xiàn)回滾UNDO操作,因為依靠ORACLE自身的UNDO機(jī)制是很慢的.

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

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

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

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

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

每個過程運行前 都要做 TRUNCATE TABLE XXXXX_TMP 的清空表的操作. 如果涉及到清單和目的表,那么要DELETE TABLE WHERE YYYY= XXXX 因為避免得到重復(fù)的數(shù)據(jù).

6 游標(biāo)批處理

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

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

cursor cur_day_result is --計算月登錄人數(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ù)三個月的購買用戶人數(shù), 日增加額和增加率, 當(dāng)天與上個月當(dāng)天的比 即同比; 月累加值.

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

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

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

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

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

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

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

把不屬自己的字段值0

最后 匯總表在移動結(jié)果表時

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

8 寬表 行轉(zhuǎn)列

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

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

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

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

那么這個表只有1百行的記錄

select 用戶

from table

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

9 報表分等級

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

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

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

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

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

C級報表 在下午下班6:00前 這個就是普通員工

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

10 RAC集群

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

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

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

C節(jié)點報表節(jié)點 ; 主要跑各個報表的任務(wù)過程

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

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

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

B 節(jié)點 要做24小時數(shù)據(jù)插入工作 也要單獨使用一個

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

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

7 分區(qū)表

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

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

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

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

11 索引

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

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

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