Oracle數(shù)據(jù)表分區(qū)的策略

1、oracle數(shù)據(jù)表分區(qū)的策略oracle雖然是一個大型的dbms,但如果不對記錄比較多的表進(jìn)行處理，仍然發(fā) 揮不了 oracle管理大型數(shù)據(jù)的強(qiáng)大功能，因此對某些表進(jìn)行分區(qū)，具有如下優(yōu) 占八、分區(qū)表中每個分區(qū)可以在邏輯上認(rèn)為是一個獨(dú)立的對象；可以在一個表中的一個或多個分區(qū)上進(jìn)行如刪除、移動、析分等維護(hù)操作， 而不會影響其它分區(qū)，具有分區(qū)獨(dú)立性；如果選擇合適的分區(qū)策略，會大大的加快數(shù)據(jù)的查詢速度。一找出需分區(qū)的表本節(jié)描述通過統(tǒng)計(jì)分析出醫(yī)院his系統(tǒng)需分區(qū)的表，對需分區(qū)的表找出包括 在你的分區(qū)鍵中的列（表的屬性），即選擇分區(qū)鍵。1、基于訪問頻度找出需分區(qū)的表oraclcsi允許訪問數(shù)據(jù)庫小的審核信

2、息，借助于收集的審核信息，設(shè)計(jì)者 能夠確定哪些表的數(shù)據(jù)是真止最頻繁訪問的，即找岀那些表，需要進(jìn)行分區(qū)。打開審核：在作為sys或sysstem登錄到數(shù)據(jù)庫后，動行如下腳本，打開對 象的審核功能。set echo off feed off ver off pages 0spool audon. sqlselect ' audit select on ' |owner i i' ' i i object name|' by access;'from dba_objectswhere object_type in (' vtew, , '

3、 tablej ) and owner in('ordadm');spool offset echo on feed on ver on這些代碼的運(yùn)行將產(chǎn)生“audon. sql”的輸出文件，它包含下而清單小所示 格式的語句audit select on ordadm. doctor orders by access; audit select on ordadm. group_order_items by access;aud itselect on ordadm. group_order_master by access;audi tselecton ordadm. ord

4、ers by access;audi t select on ordadm. orders_costs by access;audit select on ordadm. orders_s1ieet_image by access; audit select on ordadm. vital_signs_rec by access;使用命令audon. sql激活上述代碼以打開審核功能收集收集審核信息，建 立一個表以保存概要信息：create table aud_summary ( obj name varchar2 (30), owner varchar2(30), hits number)

5、;將審核信息從dba_audit_object表中取并裝入概要表中:insert into aud summaryselect obj name, owner, count (*) from dba_aud i t_objectgroup by obj_name, owner;關(guān)閉審核:set echo off feed off ver off spool audoff. sqlselect ' noaudit select on ' by access;'from dba objectswhere object_type in c view" ('o

6、rdadm');spool offset echo on feed on ver onpages 0owner ii'.' |object_name,'table' ) and owner in這些代碼的運(yùn)行將產(chǎn)生“audoff. sq的輸出文件。使用命令audoff. sql激活上述代碼以關(guān)閉上述對象的審核功能。 清除審核信息：分析審核信息col obj_name form a30 col owner form a20 col hits form 99， 990selec obj_name, owner, hi is from aud_summary;

7、 obj_name owner count(*)doctor_orders ordadm 30309 drug_stock pharmacy 11094 group_order_items ordadm 1030 group_order_master ordadm 1196 orders ordadm 40421 orders_costs ordadm 10109以上是his系統(tǒng)臨床醫(yī)囑部分24小時內(nèi)對表的訪問情況，從上而的查詢得 表 l-lo表1-1表行數(shù)命中率doctor_orders205270930309drug_stock251111094group_order_i tem38001

8、030group_order_master1861196orders163301040421orders_costs240321410109group order master（醫(yī)囑套支主記錄）、group order item（醫(yī)囑套支明 細(xì)），表的行數(shù)比較少，不適合分區(qū)；drug.stock （藥品庫存）雖然存取頻率比 較高，但表的行數(shù)比較少，因此也不適合分反。我們選擇表的行數(shù)比較多、存取 頻率比較高的表作分區(qū)處理，女ii cloctor_otders、orders> orders_costs,考慮 到doctor orders是醫(yī)生工作站上醫(yī)生開的醫(yī)囑，orders是由doctor

9、 orders 生成、護(hù)丄工作站上執(zhí)行的醫(yī)囑，兩個表結(jié)構(gòu)類似，而醫(yī)囑與藥品、衛(wèi)生材料、 計(jì)費(fèi)聯(lián)系比較密切的是orders,因此重點(diǎn)介紹對 表orders的處理。2、基于列值選擇分區(qū)鍵使用sql*plus下用命令analyze收集末分區(qū)表的統(tǒng)計(jì)信息，按照oracle 推薦的取樣20%進(jìn)行分析，并將統(tǒng)計(jì)結(jié)果保存在數(shù)據(jù)字典中。analyze table ordadm. orders estimate statistics sample 20 percenl;對dba_tab_columns數(shù)據(jù)字典視圖進(jìn)行查詢 產(chǎn)生表1-2 select table_name, column_name, num_di

10、stinet from dba_tab_columnswhere owner like ' ordadm'1列不同的 值patient_id28720vtsit_id2order_no395order_sub_no10order_class9order_code825order_text1551ordering_dept15start_date_time1194176stop_date_time636798r從表1-2中，我們可以看到欲分區(qū)表的各個候選分區(qū)鍵的分布頻譜， order_code （醫(yī)囑代碼）鍵值沒有岀現(xiàn)一種均勻分布，用它作分區(qū)鍵，明顯不合 適；order_clas

11、s （醫(yī)囑類別代碼）、ordertng_dept （開醫(yī)囑科室代碼）,鍵 值出現(xiàn)均勻小布，如用它的各個鍵值作基于范圍皿分區(qū)，每個分區(qū)具有的記錄 數(shù)比較均勻，但這種方法對于每天增加上萬條記錄的表來看，顯然不是最優(yōu)的。 如果選用start_date_time （醫(yī)囑開始時間）建立范 圍分區(qū),每月的數(shù)據(jù)建立 一個分區(qū)，在每個分f內(nèi)基于ordering_dept建立散列了分區(qū)，每月的數(shù)據(jù)形成 一個組合分區(qū)，會使每個分區(qū)的記錄數(shù)分布均勻、查詢速度提高、易于備份和 刪除。因?yàn)榇蠖鄶?shù)的統(tǒng)計(jì)和查詢是在一個月的范圍內(nèi)，而且從實(shí)際的查詢效果看, 跨月和跨年數(shù)據(jù)的統(tǒng)計(jì)和查詢速度，也比未分區(qū)的時候大大縮短，效果非常明

12、 顯。二分區(qū)表的定義create table orders (patient id varchar2(10) not null, order no number(4), order_sub_no number (2),order_class char(l), order text varchar2 (80), order_code varchar2 (10),start_date_ttme date stop_date_time dateorder1ng_dept varchar2(8)storage (initial 100m next im)partition by range(start

13、_date_ttme)subpartition by i1ash(order_code)subpartitions 3 store in (ordcrsubl, ordersub 2, ordersub 3) (partition orders200212 values less than (to_datec 2003-01-01 00:00: 00' , ' yyyy-mm-dd hh24:mi:ss')tablespace tsp_0rders,partition orders200301values less than (to date(' 2003-02

14、-01 00:00:00' , ' yyyy-mm-dd hh24:mi:ss,)tablespace tsp_orders,partition orders 200302values less than (to_date(, 2003-03-01 00:00:00，' yyyy-mm-dd hh24:nii:ss')tablespace tsporders,partition orders200402values less than (to_date(? 2004-03-01 00:00: 00，' yyyy-mm-dd hh24:nii:ss'

15、;)tablespace tsporders(subpartition ordersubl tablespace tsp_orders, subpartition ordersub 2 tablespace tsp_0rders,subpartition ordersub3 tablespace tsp_0rders);在建立的orders表中，按每月一個分區(qū)，從2002年12月開始，2004年 02 月止,共 15 個分區(qū),partition by range(start_date_time) t句，表示 用表的start_date_t1me屈性建立范圍分區(qū)，具體的每個分區(qū)名、時間范圍，在

16、partition 子府定義；subpartition by hash (order_code)子句，表示用表的 order_code屬性，在每個分區(qū)下建立散列子分區(qū)，具體的每個子分區(qū)名、所在 表空間，在ubpartttton子句定義。用下而alter table數(shù)據(jù)定義語句為orders建立約束和索引。alter table orders add constraint pkorders key (patientid, visit.id, order_no, 0rder_sub_no) using index pctfree 5 tablespace表 1-3 是用 select count

17、(*) from orders partition (分區(qū)名)語句，查 詢出的名分區(qū)的記錄數(shù)。表1-3分區(qū)名記錄數(shù)orders20030187311orders220030287553ordcrs2200303107533orders200401124812三分區(qū)表的維護(hù)操作增添分區(qū)：alter table orders add partition orders200403aalucs less (to datc(' 2004-04-01 00:00:00j ' yyyy-mm-dd hh24: mi:ss,)tablespace tsp_orders;刪除分區(qū)：alter table table name drop partition partition name;裁斷分區(qū)：al ter tab 1 e table_name trim cate part i t ion part i t i or)_r)ame storage;oracle數(shù)據(jù)表分區(qū)小知識：為了簡化數(shù)據(jù)庫大表的管理，0 r er c 1 c 8及以后版本推出了分區(qū)選項(xiàng)。 分區(qū)將表分離在若干不同的表空間上，用分i佃治z的方法來支撐無限膨脹的大 表，給大表在物理一級的可管理性。將大表分割成較小的分區(qū)可以改善表的維 護(hù)、備份、恢復(fù)、事務(wù)及查詢性能。針對當(dāng)前社保及電信行業(yè)的大量日常