如何利用Oracle表分區(qū)技術建設ODS平臺

摘要:該單位目前要在業(yè)務系統(tǒng)和數(shù)據(jù)倉庫之間建立一個ODS數(shù)據(jù)交換平臺。此ODS數(shù)據(jù)平臺用于存放從各個業(yè)務系統(tǒng)直接抽取出來的數(shù)據(jù)，這些數(shù)據(jù)從數(shù)據(jù)結構、數(shù)據(jù)之間的邏輯關系上都與原業(yè)務系統(tǒng)基本保持一致，在ODS數(shù)據(jù)平臺中利用SCHEME方式進行區(qū)分。到目前為止，我單位的業(yè)務數(shù)據(jù)已運行多年，基本信息數(shù)據(jù)、歷史歸檔數(shù)據(jù)龐大。如何規(guī)劃一個設計合理、運行安全、查詢快捷、性能高效的ODS數(shù)據(jù)交換平臺就顯得尤其重要。該文詳細論述了如何利用oracle的表空間、表分區(qū)、子分區(qū)技術并結合我單位目前業(yè)務數(shù)據(jù)的特點來建立一個高效、安全、穩(wěn)定的ODS數(shù)據(jù)交換平臺。

關鍵詞:數(shù)據(jù)庫;oracle;表分區(qū);子分區(qū);表空間;LOCAL索引

中圖分類號:TP311 文獻標識碼:A文章編號:1009-3044(2010)11-2570-03

How to Use Oracle Table Partition Technology to Build ODS Platform

LI Yong-qiang

(Chongqing Rural Commercial Bank， Chongqing 400020， China)

Abstract: We are currently building an ODS platform between the business systems and the data warehouses. This ODS data platform is used to store the data from various business systems， whose structures and logical relationships are roughly same as those of the original ones. The data in the ODS platform can be distinguished by the use of SCHEME approach. So far， the business systems have been running for many years， the basic information data and historical archive data is very huge. Therefore， it is so important to design an efficeient ODS data exchage platform with excellent structure， safe operation and fast query performance. This paper describes how to use the oracle table space， table partitioning， subpartitioning technology to create an efficient， safe， stable ODS data exchange platform.

Key words: database; partition; subpartition; tablespace; local index

表空間:是一個或多個數(shù)據(jù)文件的集合，所有的數(shù)據(jù)對象都存放在指定的表空間中，但主要存放的是表， 所以稱作表空間。

分區(qū)表:當表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應用程序的性能就會下降，這時就應該考慮對表進行分區(qū)。表進行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。

1 Oracle提供了以下幾種分區(qū)類型

1)范圍分區(qū)(range);

2)哈希分區(qū)(hash);

3)列表分區(qū)(list);

4)范圍-哈希復合分區(qū)(range-hash);

5)范圍-列表復合分區(qū)(range-list)。

ODS(Operational Data Store)是數(shù)據(jù)倉庫體系結構中的一個可選部分，ODS具備數(shù)據(jù)倉庫的部分特征和OLTP系統(tǒng)的部分特征，它是“面向主題的、集成的、當前或接近當前的、不斷變化的”數(shù)據(jù)。

2 ODS有四種類型

1)類ODS，與應用系統(tǒng)的數(shù)據(jù)延遲為1～2秒，實時或近似實時;

2)類ODS，與應用系統(tǒng)的數(shù)據(jù)延遲為2～4小時;

3)類ODS，與應用系統(tǒng)的數(shù)據(jù)延遲為12～24小時;

4)類ODS，數(shù)據(jù)倉庫中部分決策分析數(shù)據(jù)回流至ODS中。

可以說，數(shù)據(jù)延遲時間越短，ODS建設難度越高，其中I類ODS的建設難度最高，建設成本也是最高的。從現(xiàn)階段的應用來看，我單位的ODS建設目標是III類，ODS數(shù)據(jù)與應用系統(tǒng)的數(shù)據(jù)延遲保持在24小時左右。

3 現(xiàn)狀分析

目前，我單位系統(tǒng)內(nèi)部有很多的子系統(tǒng)，如核心業(yè)務系統(tǒng)數(shù)據(jù)RUCCDTA、核心業(yè)務系統(tǒng)參數(shù)RUCCCMN、中間業(yè)務AGENT、支付系統(tǒng)數(shù)據(jù)、反洗錢、支票影像、身份證核查、客戶信息CIF等多個系統(tǒng)平臺等。他們之間的信息存在著重復和不一致的情況，如客戶資料、信貸數(shù)據(jù)等。如何綜合應用這些數(shù)據(jù)，首要任務就是進行數(shù)據(jù)整合，ODS的特點恰恰可以擔當起這付重任。

下面以核心業(yè)務系統(tǒng)數(shù)據(jù)RUCCDTA平臺內(nèi)CMVCA、SVDSA表為例，介紹如何利用oracle數(shù)據(jù)庫的range分區(qū)及range子分區(qū)特性即范圍-范圍復合分區(qū)(range-range)技術，建設ODS數(shù)據(jù)交換平臺。

4 表在oracle數(shù)據(jù)庫中的規(guī)劃設計

圖1為ODS系統(tǒng)架構圖。

4.1 設計說明

1)建立系統(tǒng)默認的表空間、索引表空間，不需要分區(qū)的表建立在此給定的空間上

2)建立40個表空間(每個機構對應一個表空間，其原因是利于數(shù)據(jù)的保存，以后可以針對每個機構進行數(shù)據(jù)導入和導出，也利于針對每個機構的數(shù)據(jù)查詢)

3)創(chuàng)建數(shù)據(jù)表，以機構range作分區(qū)，每個表共有40個表分區(qū)

4)在每個機構表分區(qū)中再按照日期中的年range進行子分區(qū)，從系統(tǒng)上線的2003年到現(xiàn)在2009年共7年。因此每個表的分區(qū)共有:40個表分區(qū)*7=280個分區(qū)

5)對每一個表建立LOCAL索引(相當于在每一個微分區(qū)內(nèi)建立索引)

6)每個最小分區(qū)的數(shù)據(jù)量:以CMVCA表為例，400G/40個機構=10G/每機構/每年。每個子分區(qū)的數(shù)據(jù)量不大，利于數(shù)據(jù)的導出、保存及導入。

4.2 詳細操作步驟

4.2.1 創(chuàng)建系統(tǒng)缺省表空間:(用戶默認表空間)

$SQLPLUS / as sysdba

//數(shù)據(jù)表空間

SQL>CREATE TABLESPACE ts_ruccdta DATAFILE ‘+HISDATA/’ SIZE 30G;

//索引表空間

SQL>CREATE TABLESPACE idx_ruccdta DATAFILE ‘+HISDATA/’ SIZE 30G;

4.2.2 用戶

創(chuàng)建用戶:

SQL>CREATE USER ruccdta IDENTIFIED BY ruccdta DEFAULT TABLESPACE ts_ruccdta;

用戶授權:

SQL>GRANT connect， resource TO ruccdta;

SQL>commit;提交

4.2.3 創(chuàng)建分區(qū)表空間

表空間腳本:crtspace_dta.sql，根據(jù)機構數(shù)量生成對應的表空間

$sqlplus / as sysdba

SQL>CREATE TABLESPACE TS_SBNO01 DATAFILE ‘+HISDATA/’ SIZE 30G;

SQL>CREATE TABLESPACE TS_SBNO02 DATAFILE ‘+HISDATA/’ SIZE 30G;

SQL>CREATE TABLESPACE TS_SBNO39 DATAFILE ‘+HISDATA/’ SIZE 30G;

SQL>CREATE TABLESPACE TS_SBNO40 DATAFILE ‘+HISDATA/’ SIZE 30G;

4.2.4 創(chuàng)建分區(qū)表

1)CMVCA交易日志文件腳本(帶表分區(qū)VCSBNO、子分區(qū)VCTRDT)

$sqlplus ruccdta/ruccdta

create table CMVCA(

VCVGNONUMBER(8)default 0 not 1，

VCTRDTdatedefault sysdate

VCSBNOVARCHAR(6) default ' '，

VCWSNOVARCHAR(8) default ' '，

VCTRSNVARCHAR(6) default ' '，

VCCYNOVARCHAR(2) default ' '，

VCITEMVARCHAR(5) default ' '，

VCCUTYVARCHAR(1) default ' ')

PARTITION BY RANGE(VCSBNO)

SUBPARTITION BY RANGE(VCTRDT)

SUBPARTITION TEMPLATE

(SUBPARTITION SUB2002 VALUESLESS THAN(TO_DATE('01/01/2003'，'MM/DD/YYYY'))，

SUBPARTITION SUB2003 VALUESLESS THAN(TO_DATE('01/01/2004'，'MM/DD/YYYY'))，

SUBPARTITION SUB2008 VALUESLESS THAN(TO_DATE('01/01/2009'，'MM/DD/YYYY'))，

SUBPARTITION SUB2009 VALUESLESS THAN(TO_DATE('01/01/2010'，'MM/DD/YYYY')))

PARTITION RUCCDTA01 VALUES LESS THAN ('020000') TABLESPACE TS_SBNO01，

PARTITION RUCCDTA02 VALUES LESS THAN ('030000') TABLESPACE TS_SBNO02，

PARTITION RUCCDTA39 VALUES LESS THAN ('400000') TABLESPACE TS_SBNO39，

PARTITION RUCCDTA40 VALUES LESS THAN ('410000') TABLESPACE TS_SBNO40);

2)交易細文件腳本(表分區(qū)DSSBNO、子分區(qū)DSTRDT)

SVDSA客戶$sqlplus ruccdta/ruccdta

create table SVDSA

(DSACNO VARCHAR(19) default ' ' not 1，

DSTRDT datedefault sysdate，

DSSBNO VARCHAR(6)default ' ' not 1，

DSUSID VARCHAR(6)default ' ' not 1，

DSAUUS VARCHAR(6)default ' ' not 1，

DSTLSQ VARCHAR(15) default ' ' not 1，

DSCCNO VARCHAR(13) default ' ' not 1，

DSNUBR NUMBER(8) default 0 not 1，

DSBCKD VARCHAR(2)default ' ' not 1)

PARTITION BY RANGE (DSSBNO)

SUBPARTITION BY RANGE(DSTRDT)

SUBPARTITION TEMPLATE

(SUBPARTITION SUB2002 VALUESLESS THAN(TO_DATE('01/01/2003'，'MM/DD/YYYY'))，

SUBPARTITION SUB2009 VALUESLESS THAN(TO_DATE('01/01/2010'，'MM/DD/YYYY')))

( PARTITION RUCCDTA01 VALUES LESS THAN ('020000') TABLESPACE TS_SBNO01，

PARTITION RUCCDTA02 VALUES LESS THAN ('030000') TABLESPACE TS_SBNO02，

PARTITION RUCCDTA39 VALUES LESS THAN ('400000') TABLESPACE TS_SBNO39，

PARTITION RUCCDTA40 VALUES LESS THAN ('410000') TABLESPACE TS_SBNO40)。

5 創(chuàng)建表索引

//按機構號、交易日期、帳號(vctrsn，vccyno，vcitem，vcacsq)建立分區(qū)索引

create index idx_cmvca on cmvca(vcsbno，vctrdt，vctrsn，vccyno，vcitem，vcacsq) local tablespace idx_sbno;

//按機構號、交易日期、帳號、交易序號建立唯一分區(qū)索引

create index idx_svdsa on svdsa(dssbno，dstrdt，dsacno，dsnubr) local tablespace idx_sbno;

6 結束語

至此，大數(shù)據(jù)表的在Oracle數(shù)據(jù)庫中分區(qū)設計及實施已完成，此操作步驟已經(jīng)在我單位的ODS建設中完全驗證正確。本文的目的也是為了向其他在ODS建設過程中遇到類似問題的同行提供一點借鑒，希望能對他們有所幫助，也希望他們在大數(shù)據(jù)表處理過程中能夠建設起結構清晰數(shù)據(jù)交換平臺。

參考文獻:

[1] 18 Partitioned Tables and Indexes[EB/OL].http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10743/partconc.htm.

[2] Oracle Database Concepts 10g Release 1 (10.1) Part Number B10743-01(18 Partitioned Tables and Indexes)[EB/OL].http://www.oracle.com/technology/documentation/database10g.html.

[3] Oracle Database Concepts 10g Release 2 (10.2) Part Number B14231-02(17 Managing Partitioned Tables and Indexes)[EB/OL].http://www.oracle.com/pls/db102/homepage.