詳解Oracle資料貨場中三種最佳化：分區、維度和物化視圖

標籤：

轉 xiewmang 新浪部落格 

本文主要介紹了Oracle資料貨場中的三種最佳化：對分區的最佳化、維度最佳化和物化視圖的最佳化，並給出了詳細的最佳化代碼，希望對您有所協助。

 

我們在做資料庫的項目時，對資料貨場的最佳化，大約的原理只有兩個：一是資料分塊儲藏，便於資料的轉儲和管教；二是其中處理，長進資料供給的速度。本文主要介紹了Oracle資料貨場中的三種最佳化：對分區的最佳化、維度最佳化和物化視圖的最佳化，基上面兩個大約的原理，藉助於資料貨場的觀念，羅列資料庫的最佳化措施：
1．分區
在資料貨場中，事實表，索引表，維度資料表分處於三個不同的資料表空間其中（在安排的時候，良好是安排到不同的磁碟上）。這麼子做的起因即便便於並發壟斷，其實資料貨場和等閑的資料庫之間未曾嚴厲的界限，重要還是安排上，當然oracle本身會為資料貨場中的“事實表”產生“知識庫”等壟斷，保證更快的資料供給效率，其實能夠藉助於job和表面過程來調動預存程序告終。基於這個思路，本項目資料庫中的索引將和事實表離別維護，當然這稱不上分區（）。懇摯的分區是指下面的內容。
分區即便partition/subpartition，對於事實表本身來說，以月為單位作partition掛載到不同資料表空間上。翔實的樣本如下：

1. create table T_LOGNODE_RECORD  
2. (  
3. ……  
4. CALLIN_HH NUMBER(2) not null,  
5. CALLIN_DD NUMBER(2) not null,  
6. CALLIN_MONTH NUMBER(2) not null,  
7. CALLIN_YEAR NUMBER(4) not null,  
8. MONTH_MOD NUMBER(1) not null  
9. )  
10. PARTITION BY LIST (MONTH_MOD)  
11. (  
12. PARTITION P0 VALUES (0) TABLESPACE TS0,  
13. PARTITION P1 VALUES (1) TABLESPACE TS1,  
14. PARTITION P2 VALUES (DEFAULT) TABLESPACE TS2  
15. ); 

上述的代碼中以MONTH_MOD欄位作為分區規範，將T_LOGNODE_RECORD分到三個不同的資料表空間（TS0，TS1，TS2）中，這裡有一個技巧，MONTH_MOD = 月份mod3,這麼子能夠無須要人工的維護哪個月分入哪個分區。當然容易的以月作為分區爾後，對於我們目前的資料庫來說還是遠遠不夠的，因為即便是一個月的資料依舊是很宏偉的，那麼必需我們做SUBPARTITION，做法的樣本為：

1. create table T_LOGNODE_RECORD  
2. (  
3. ……  
4. CALLIN_DD NUMBER(2) not null,  
5. MONTH_MOD NUMBER(1) not null  
6. )  
7. PARTITION BY RANGE(MONTH_MOD)  
8. SUBPARTITION BY LIST(CALLIN_DD)  
9. SUBPARTITION TEMPLATE  
10. (  
11. SUBPARTITION SUBP1 values (1),  
12. SUBPARTITION SUBP2 values (2),  
13. SUBPARTITION SUBP3 values (3)  
14. )  
15. (  
16. PARTITION P0 VALUES less than (1),  
17. PARTITION P1 VALUES less than (2),  
18. PARTITION P2 VALUES less than (3)  
19. ); 

分區措施有三種：RANGE（範圍），LIST（列表分區），HASH（雜湊分割），必需解釋的HASH分區，是oracle調用內建的hash函數來告終對資料的平衡散布，保證分區內部的資料量雷同，因而只必需制訂分區的數量即可。
對於複合分區來說。不是所有分區款式都能夠嵌套的，oracle只扶持範圍列表分區，範圍雜湊分割，尤其的定界分割能夠扶持多個欄位range by(field1,field2)。對於本項目管用到的分區措施，只有樣本中給出的兩種，至於分區的利益，請察看google,^_^。給出壟斷的sql樣本如下：

1. select * from T_LOGNODE_RECORD partition(P0)  
2. update T_LOGNODE_RECORD partition(P0) t set…… 

insert語句未曾波及。當然上述的壟斷能夠像等閑的Sql語句一樣，不指定分區也能夠舉行，但在查詢的時候請盡量能確定分區和子分區subpartition，這麼子會讓查詢的速度長進百倍。能夠剔除指定的分區drop可能truncate, EXPORT和IMPORT分區中資料，然而當舉行分區的剔除壟斷的時候，會使大局的索引（index）實效，必需重興發生。
2．維度
Dimension這個算是oracle的進階功能了,在PLSQL Developer未曾措施找到這個對象（然而能夠厲行語句來產生該對象，然而無法舉行後期的維護），只能在Toad中有它的身影。它是闡明資料維度欄位之間的大小聯絡。標兵的慷慨是下面的年月日了。

1. create table DIM_TIME  
2. (  
3. D_YEAR VARCHAR2(4) not null,  
4. D_QUATER VARCHAR2(2) not null,  
5. D_MONTH VARCHAR2(2) not null,  
6. D_DAY DATE not null  
7. )  
8. CREATE DIMENSION DIM_TIME  
9. LEVEL YEAR IS (DIM_TIME.D_YEAR)  
10. LEVEL QUATER IS (DIM_TIME.D_QUATER)  
11. LEVEL MONTH IS (DIM_TIME.D_MONTH)  
12. LEVEL DAY IS (DIM_TIME.D_DAY)  
13. HIERARCHY Y_Q_M_D  
14. (  
15. DAY CHILD OF  
16. MONTH CHILD OF  
17. QUATER CHILD OF YEAR  
18. )  
19. HIERARCHY Y_M_D  
20. (  
21. DAY CHILD OF  
22. MONTH CHILD OF YEAR  
23. ) 

發生了兩條繼承聯絡Y_Q_M_D和Y_M_D。當DIM_TIME本身的普查，以及與其他表作聯集查詢然後匯總時，月的摘要資料積極以日匯總的資料為基礎，這麼子大大長進資料匯總的速度。
可感受一張表發生多個維度資料表，一個維度中能夠有多個繼承聯絡。維度極大程度上長進了資料匯總的速度，使我們這個項目標資料普查分析的利器。
3.  物化視圖（快照）
物化視圖MATERIALIZED VIEW。划算開心的是PLSQL Developer和Toad上同時具有了這個對象的存在（Toad上可能將這個稱為快照snapshots），然而令人煩悶的事情發生了，在PLSQL Developer上寫成的MATERIALIZED VIEW在Toad中無法分辨，看來還要兩個都用能力夠。
物化視圖，望文生義具有視圖的屬性，它是發生在切實表的基礎上，將切實表中的資料籠統揭示，甚至創立的措施都幾乎一樣。然而它成為物化，就意味著它是實體物件，從某種含義上將，它更像一個其中表，能夠像表格一樣直接查詢其中的資料（甚至就在table列表中能夠看到它），然而它不但僅是其中表，因為它能夠波及原始表的資料，並且oracle能夠積極將針對原始表的查詢匯總語句直接重新導向到物化視圖上，來晉級查詢的速度。

1. CREATE MATERIALIZED VIEW MV_LOG_RECORD  
2. REFRESH FORCE  
3. ON DEMAND  
4. ENABLE QUERY REWRITE  
5. AS  
6. SELECT tcmy8.com. NODE_ID, Count(*) as Call_Times  
7. from T_LOGNODE_RECORD t  
8. GROUP BY t. T_LOGNODE_RECORD 

上面的SQL語句就曾經在T_LOGNODE_RECORD表上發生一個物化視圖。REFRESH的參數 FORCE表示迫使重新整理，還能夠抉擇的參數有fast和complete兩種，fast是最快的重新整理措施，表示增量重新整理（要做到增量重新整理，定然在物化視圖中包括單一性的標明的字元，如主鍵和rowid等，當然即便包括了也無須定能夠做到迅速重新整理，因為我們項目中未曾波及到要重新整理的物化視圖，在此就不多言了）；complete表示全面的資料重新整理，重建物化視圖；force是抉擇性的重新整理，依據切實的情形來抉擇是force還是complete。
ON DEMAND表示重新整理引發的條件。上面的物化視圖只有在使用者發動查詢哀求時，才會去重新整理資料，即刻原始表中的資料取到物化視圖的表中。還有一種措施即便 ON COMMIT，當使用者壟斷未經處理資料時，就重新整理對應的物化視圖，這種措施合乎對於及時性要求高的項目。萬一必需做定時的重新整理，即將用到下面的措施lr.lrheicha.com：
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1/48。
物化視圖是能夠橫亙多個表的，也能夠直接的查詢物化試圖，例如你能夠：
Select * from MV_LOG_RECORD.

歸納
在上述的三種措施中，屬於資料庫功能最佳化的重要手段，當然還有許多能夠做的細節的最佳化，例如盡量不要在你要查詢的欄位上面批准為空白，盡量將聯集查詢的聯絡扼製為number型等等。
對了，萬一做了維度資料表，做了物化試圖，你去查詢原始表的時候速度定然會添置嗎？答案是“不”，你要做的一個重要的事情，即便告訴oracle，它能夠積極的最佳化查詢，語句如下：

1. ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;  
2. ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED; 

關於Oracle資料貨場中三種最佳化的知識就介紹到這裡了，希望能夠帶給您一些收穫，謝謝！

詳解Oracle資料貨場中三種最佳化：分區、維度和物化視圖