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

2010-08-28 10:46:40來源:西部e網(wǎng)作者:

    本文描述通過統(tǒng)計分析出醫(yī)院信息系統(tǒng)需分區(qū)的表,對需分區(qū)的表選擇分區(qū)鍵,即找出包括在你的分區(qū)鍵中的列(表的屬性),對大型數(shù)據(jù)的管理比較有意義, 本文的工作在Oracle8.1.6下實現(xiàn)。

  Oracle雖然是一個大型的DBMS,但如果不對記錄比較多的表進行處理,仍然發(fā)揮不了Oracle管理大型數(shù)據(jù)的強大功能,因此對某些表進行分區(qū),具有如下優(yōu)點:

  分區(qū)表中每個分區(qū)可以在邏輯上認為是一個獨立的對象;

  可以在一個表中的一個或多個分區(qū)上進行如刪除、移動、析分等維護操作,而不會影響其它分區(qū),具有分區(qū)獨立性;

  如果選擇合適的分區(qū)策略,會大大的加快數(shù)據(jù)的查詢速度。

  一 找出需分區(qū)的表

  本節(jié)描述通過統(tǒng)計分析出醫(yī)院His系統(tǒng)需分區(qū)的表,對需分區(qū)的表找出包括在你的分區(qū)鍵中的列(表的屬性),即選擇分區(qū)鍵。

  1、基于訪問頻度找出需分區(qū)的表

  Oracle8i允許訪問數(shù)據(jù)庫中的審核信息,借助于收集的審核信息,設(shè)計者能夠確定哪些表的數(shù)據(jù)是真正最頻繁訪問的,即找出那些表,需要進行分區(qū)。

  打開審核:在作為SYS或SYSSTEM登錄到數(shù)據(jù)庫后,動行如下腳本,打開對象的審核功能。

set echo off feed off ver off pages 0
spool audon.sql
select ’audit select on ’||owner||’.’||object_name||’ by
access;’
from dba_objects
where object_type in (’VIEW’,’TABLE’) and owner in
(’ORDADM’);
spool off
set echo on feed on ver on

  這些代碼的運行將產(chǎn)生“audon.sql”的輸出文件,它包含下面清單中所示格式的語句

audit select on ORDADM.DOCTOR_ORDERS by access;
audit select on ORDADM.GROUP_ORDER_ITEMS by access;
audit select on ORDADM.GROUP_ORDER_MASTER by access;
audit select on ORDADM.ORDERS by access;
audit select on ORDADM.ORDERS_COSTS by access;
audit select on ORDADM.ORDERS_SHEET_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);

  將審核信息從dba_audit_object表中取出并裝入概要表中:

insert into aud_summary
select obj_name,owner,count(*)
from dba_audit_object
group by obj_name,owner;

  關(guān)閉審核:

set echo off feed off ver off pages 0
spool audoff.sql
select ’noaudit select on ’||owner||’.’||object_name|| ’
by access;’
from dba_objects
where object_type in (’VIEW’,’TABLE’) and owner in
(’ORDADM’);
spool off
set echo on feed on ver on

  這些代碼的運行將產(chǎn)生“audoff.sql”的輸出文件。

  使用命令@ audoff.sql激活上述代碼以關(guān)閉上述對象的審核功能。

  清除審核信息:

delete sys.aud$

  分析審核信息

col obj_name form a30
col owner form a20
col hits form 99,990
selec obj_name,owner,hits from aud_summary;
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)對表的訪問情況,從上面的查詢得表1-1。

  表1-1

行數(shù) 命中率
doctor_orders 2052709 30309
drug_stock 2511 11094
group_order_item 3800 1030
group_order_master 186 1196
orders 1633010 40421
orders_costs 2403214 10109

  group_order_master( 醫(yī)囑套攴主記錄) 、group_order_item(醫(yī)囑套攴明細), 表的行數(shù)比較少,不適合分區(qū);drug_stock(藥品庫存)雖然存取頻率比較高,但表的行數(shù)比較少,因此也不適合分區(qū)。我們選擇表的行數(shù)比較多、存取頻率比較高的表作分區(qū)處理,如 doctor_orders、orders、orders_costs,考慮到doctor_orders是醫(yī)生工作站上醫(yī)生開的醫(yī)囑,orders是由doctor_orders生成、護士工作站上執(zhí)行的醫(yī)囑,兩個表結(jié)構(gòu)類似,而醫(yī)囑與藥品、衛(wèi)生材料、計費聯(lián)系比較密切的是Orders,因此重點介紹對表orders的處理。

  2、基于列值選擇分區(qū)鍵

  使用Sql*plus下用命令A(yù)nalyze收集末分區(qū)表的統(tǒng)計信息,按照Oracle推薦的取樣20%進行分析,并將統(tǒng)計結(jié)果保存在數(shù)據(jù)字典中。

Analyze table ORDADM.ORDERS estimate statistics
sample 20 percent;


  對DBA_TAB_COLUMNS數(shù)據(jù)字典視圖進行查詢



  產(chǎn)生表1-2

select table_name,column_name,num_distinct
from DBA_TAB_COLUMNS
where owner like ’ORDADM’;

  表1-2

不同的值
PATIENT_ID 28720
VISIT_ID 2
ORDER_NO 395
ORDER_SUB_NO 10
ORDER_CLASS 9
ORDER_CODE 825
ORDER_TEXT 1551
ORDERING_DEPT 15
START_DATE_TIME 1194176
STOP_DATE_TIME 636798
   


  從表1-2中,我們可以看到欲分區(qū)表的各個候選分區(qū)鍵的分布頻譜,ORDER_CODE(醫(yī)囑代碼)鍵值沒有出現(xiàn)一種均勻分布,用它作分區(qū)鍵,明顯不合適;

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

    二 分區(qū)表的定義

Create Table orders (
 PATIENT_ID varchar2(10) not null, ORDER_NO number(4), ORDER_SUB_NO number(2),
 ..............
 ORDER_CLASS char(1),
 ORDER_TEXT varchar2(80),
 ORDER_CODE varchar2(10),
 .............
 START_DATE_TIME date
 STOP_DATE_TIME date
 .................
 ORDERING_DEPT varchar2(8)
 storage (initial 100M next 1M)
 PARTITION BY RANGE(START_DATE_TIME)
 SUBPARTITION BY HASH(ORDER_CODE)
 SUBPARTITIONS 3 STORE IN (ordersub1, ordersub 2, ordersub 3)
 (PARTITION orders200212 VALUES LESS THAN (to_date(’2003-01-01 00:00: 00’,’yyyy-mm-dd hh24:mi:ss’))
 tablespace Tsp_Orders,
 PARTITION orders200301
 VALUES LESS THAN (to_date(’2003-02-01 00:00: 00’,’yyyy-mm-dd hh24:mi:ss’))
 tablespace Tsp_Orders,,
 PARTITION orders 200302
 VALUES LESS THAN (to_date(’2003-03-01 00:00: 00’,’yyyy-mm-dd hh24:mi:ss’))
 tablespace Tsp_Orders,,
 .....................
 PARTITION orders200402
 VALUES LESS THAN (to_date(’2004-03-01 00:00: 00’,’yyyy-mm-dd hh24:mi:ss’))
 tablespace Tsp_Orders
 (SUBPARTITION ordersub1 TABLESPACE Tsp_Orders, SUBPARTITION ordersub 2 TABLESPACE Tsp_Orders,
SUBPARTITION ordersub3 TABLESPACE Tsp_Orders));

  在建立的Orders表中,按每月一個分區(qū),從2002年12月開始,2004年02月止,共15個分區(qū), PARTITION BY RANGE(START_DATE_TIME) 子句,表示用表的START_DATE_TIME屬性建立范圍分區(qū),具體的每個分區(qū)名、時間范圍,在PARTITION子句定義;SUBPARTITION BY HASH(ORDER_CODE)子句,表示用表的ORDER_CODE 屬性,在每個分區(qū)下建立散列子分區(qū),具體的每個子分區(qū)名、所在表空間,在UBPARTITION子句定義。

  用下面ALTER TABLE數(shù)據(jù)定義語句為Orders建立約束和索引。

ALTER TABLE Orders ADD CONSTRAINT Pk_Orders KEY (Patient_id,
Visit_id,Order_no,Order_sub_no) USING INDEX PCTFREE 5 TABLESPACE Tsp_Orders;

  表1-3是用select count(*) from orders partition (分區(qū)名)語句,查詢出的名分區(qū)的記錄數(shù)。

  表1-3

分區(qū)名 記錄數(shù)
orders200301 87311
orders2200302 87553
orders2200303 107533
............  
orders200401 124812

  三 分區(qū)表的維護操作

  增添分區(qū):

Alter Table orders Add partition orders200403
Aalues less (to_date(’2004-04-01 00:00:00’,’yyyy-mm-dd hh24: mi:ss’))
Tablespace Tsp_Orders;

  刪除分區(qū):

Alter Table table_name Drop Partition partition_name;

  裁斷分區(qū):

Alter Table table_name Truncate Partition partition_name Storage;

  Oracle數(shù)據(jù)表分區(qū)小知識:

  為了簡化數(shù)據(jù)庫大表的管理,O r a c l e 8 及以后版本推出了分區(qū)選項。分區(qū)將表分離在若干不同的表空間上,用分而治之的方法來支撐無限膨脹的大表,給大表在物理一級的可管理性。將大表分割成較小的分區(qū)可以改善表的維護、備份、恢復(fù)、事務(wù)及查詢性能。針對當(dāng)前社保及電信行業(yè)的大量日常業(yè)務(wù)數(shù)據(jù), 可以推薦使用ORACLE8 的該選項。

  分區(qū)的優(yōu)點:

  1 、增強可用性:如果表的一個分區(qū)由于系統(tǒng)故障而不能使用,表的其余好的分區(qū)仍然可以使用;

  2 、減少關(guān)閉時間:如果系統(tǒng)故障只影響表的一部分分區(qū),那么只有這部分分區(qū)需要修復(fù),故能比整個大表修復(fù)花的時間更少;

  3 、維護輕松:如果需要重建表,獨立管理每個分區(qū)比管理單個大表要輕松得多;

  4 、均衡I / O : 可以把表的不同分區(qū)分配到不同的磁盤來平衡I / O 改善性能;

  5 、改善性能:對大表的查詢、增加、修改等操作可以分解到表的不同分區(qū)來并行執(zhí)行,可使運行速度更快;

  6 、分區(qū)對用戶透明,最終用戶感覺不到分區(qū)的存在。

關(guān)鍵詞:Oracle

贊助商鏈接: