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

基本上不建議在表里建索引,采用多層分區表,實現全表掃描. 因為索引會導致反而比全掃描慢,索引在大規模數據更新的時候維護成本高. 會極大影響各個報表的運行時間.

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

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